Export Microsoft Access Database to Text File

[cass84]

Hi, I'm using Visual Basic Express 2008 (VB.NEt). I've created a project which
connect to a Microsoft Access Database (OLEDB) & displays the records using textboxes in the windows app.

I need to add a button which exports the database to a fixed length text file. Does anyone know the code I would use to do this?

[stanav]

A database contains tables. You want to export every record in every table to a single text file? And what's up with that "fixed length text file"? Do you mean fixed length records?

[cass84]

My database only contains one table. I want to export every record in that table.

Yes that is what I mean, as in not comma delimited. Each field is set at a certain amount of bytes, each row of fields ends at the same position.

[stanav]

I assume that you already know how to get the data from your database to datatable (since you already display records in the UI), but if you don't, there are plenty of tutorials and code examples around that you can read up. So I just bypass that part for now.
Let's say you have a datatable filled with data, and to export that data to a text file as fixed length record text file, you will need to know what's the length for each field.
Use a stringbuilder object to build your output and when done, you write it to a file. Something like this:

Dim sb as new system.text.stringbuilder()
For Each row as datarow in table.Rows
     'suppose field0 is type string and is 20 chars in length, so we 
     'make it 20 chars long by padding blank spaces to the right
     sb.Append(row(0).ToString.PadRight(20)) 

     'suppose field1 is type integer and is 5 chars in length, so we 
     'make it 5 chars long by padding 0's to the left (leading zero's)
     sb.Append(row(1).ToString.PadLeft(5, "0")

     'Keep doing it for all the fields
     .....
     'Then finally, append a newline to complete the record
     sb.Append(Environment.Newline())
Next
'Once you get out of the loop, you output is ready to be written to a file
IO.File.WriteAllText("C:\test.txt", sb.ToString)

Note: It depends on what's your record specification is that you choose iether padrigth or padleft on each field.... But generally, all numeric values are padleft with 0's (leading zero's won't change the value of a number) and string is padright with blank spaces.

[cass84]

Ok so lets say I have a table(table1) in a database(db1) which has the following fields:
Field 1 = 30 Bytes
Field 2 = 60 Bytes
Field 3 = 25 Bytes

The code would go something like this with no leading zeros?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sb as new system.text.stringbuilder()
For Each row as datarow in table.Rows
sb.Append(row(0).ToString.PadRight(30))
sb.Append(row(1).ToString.PadRight(60))
sb.Append(row(2).ToString.PadRight(25))
sb.Append(Environment.Newline())
IO.File.WriteAllText("C:\test.txt", sb.ToString)
End Sub

Thanks so much for your help!

[cass84]

I assume that you already know how to get the data from your database to datatable (since you already display records in the UI), but if you don't, there are plenty of tutorials and code examples around that you can read up. So I just bypass that part for now.
Let's say you have a datatable filled with data, and to export that data to a text file as fixed length record text file, you will need to know what's the length for each field.
Use a stringbuilder object to build your output and when done, you write it to a file. Something like this:

Dim sb as new system.text.stringbuilder()
For Each row as datarow in table.Rows
     'suppose field0 is type string and is 20 chars in length, so we 
     'make it 20 chars long by padding blank spaces to the right
     sb.Append(row(0).ToString.PadRight(20)) 

     'suppose field1 is type integer and is 5 chars in length, so we 
     'make it 5 chars long by padding 0's to the left (leading zero's)
     sb.Append(row(1).ToString.PadLeft(5, "0")

     'Keep doing it for all the fields
     .....
     'Then finally, append a newline to complete the record
     sb.Append(Environment.Newline())
Next
'Once you get out of the loop, you output is ready to be written to a file
IO.File.WriteAllText("C:\test.txt", sb.ToString)

Note: It depends on what's your record specification is that you choose iether padrigth or padleft on each field.... But generally, all numeric values are padleft with 0's (leading zero's won't change the value of a number) and string is padright with blank spaces.

Ok so lets say I have a table(table1) in a database(db1) which has the following fields:
Field 1 = 30 Bytes
Field 2 = 60 Bytes
Field 3 = 25 Bytes

The code would go something like this with no leading zeros?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sb as new system.text.stringbuilder()
For Each row as datarow in table.Rows
sb.Append(row(0).ToString.PadRight(30))
sb.Append(row(1).ToString.PadRight(60))
sb.Append(row(2).ToString.PadRight(25))
sb.Append(Environment.Newline())
IO.File.WriteAllText("C:\test.txt", sb.ToString)
End Sub

Thanks so much for your help!

[stanav]

Did you tried it? If you haven't, I suggest that you do now. That's the only way to find out if it works or not. Come back and post more questions if it does not work.

[cass84]

Did you tried it? If you haven't, I suggest that you do now. That's the only way to find out if it works or not. Come back and post more questions if it does not work.

When I tried the code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sb As New system.text.stringbuilder()
        For Each row As DataRow In DataTable.Rows
        Next
        sb.Append(row(0).ToString.PadRight(30))
        sb.Append(row(1).ToString.PadRight(60))
        sb.Append(row(2).ToString.PadRight(25))
        sb.Append(Environment.NewLine())
        IO.File.WriteAllText("C:\test.txt", sb.ToString)
    End Sub

I received the following errors:
Error 1 Reference to a non-shared member requires an object reference.
Error 2-4 Name 'row' is not declared.

[stanav]

If you go back and re-read post#4, you'll see that the very 1st sentence I wrote:

I assume that you already know how to get the data from your database to datatable (since you already display records in the UI)

So that table in mycode refers to the datatable that you already use to display records in your application.

[cass84]

Your assumption was incorrect. I am only a beginner at this. I connected the database to the project & dragged the fields.

Are you saying that I need to change "DataTable.Rows" to "CData.Rows" with "CData" being the name of my table? I tried that and this time I get "CData is not declared"

[stanav]

Can you post your form load code?

[cass84]

Here it is...

Option Explicit On
Public Class CForm

    Private Sub CdataBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CdataBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CdataBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CdbDataSet)

    End Sub

    Private Sub CForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'CdbDataSet.Cdata' table. You can move, or remove it, as needed.
        Me.CdataTableAdapter.Fill(Me.CdbDataSet.Cdata)

    End Sub
End Class

[stanav]

OK, so your datatable is Me.CdbDataSet.Cdata
Try changing DataTable.Rows with Me.CdbDataSet.Cdata.Rows and see if it works.

[cass84]

Thanks, that fixed the object reference. Now i'm still receiving the error in lines 5-7.

Name 'row' is not declared.

[stanav]

Look closer to the code you have... You have nothing running in the loop

 Dim sb As New system.text.stringbuilder()
        For Each row As DataRow In DataTable.Rows
        Next
        sb.Append(row(0).ToString.PadRight(30))
        sb.Append(row(1).ToString.PadRight(60))
        sb.Append(row(2).ToString.PadRight(25))
        sb.Append(Environment.NewLine())
        IO.File.WriteAllText("C:\test.txt", sb.ToString)

You need to move the "Next" line down to right above the line IO.File.WriteAllText...

[cass84]

Thanks it worked! I just have one more question. I need to join 2 of the fields from the table and export them as one unique value. I've modified the code like:

sb.Append(row(0) + row(1).ToString.PadRight(30))

But that doesn't ensure that the value exported is unique.

I've also tried creating a query in the database joining the fields but I can't seem to get the joined field from the query to be displayed. I tried the below:

For Each row As DataRow In Me.CdbDataSet.cq.Rows
            sb.Append(row(0).ToString.PadRight(12))
        Next
        IO.File.WriteAllText("C:\download.txt", sb.ToString)
        For Each row As DataRow In Me.CdbDataSet.cdata.Rows
            sb.Append(row(1).ToString.PadRight(2))
        Next
        IO.File.WriteAllText("C:\download.txt", sb.ToString)
 End Sub

This hasn't worked for me because VisualBasic sees Queries as Views & I can't get the data to save for the joined field and export it to the file.

Do you know how I could get this to work?

[stanav]

Suppose you want to join field1 and field2 together and the resulting field should have a fixed field length of 30 bytes, you do this:

sb.Append((row(0).ToString & row(1).ToString).PadRight(30))

Pay close attention to the parenthesis.... You'll see that I concatenate field1 and field2 together 1st, and then padleft on the resulting string.

[cass84]

Yes I understand that... but the problem is that I need the resulting concatenated field to contain unique data such as:

Row 0 Row1
01 100
01 200
02 100
02 100

Becomes:
01100
01200
02100 **
02100 **

Where the ** wouldn't be allowed because it's not a unique number.

[stanav]

Use a list(of string) to temporary hold the joined fields, and when done, write that list to a file. The reason to use a list is that you can test to see if a particular item is already in the list or not. If it is not, you add it to the list. If it is, you just kip it.
Some thing like this:

Dim tempList As New List(Of String)
Dim item As string = string.Empty
For Each row As DataRow In Me.CdbDataSet.cq.Rows
     item = (row(0).ToString & row(1).ToString).PadRight(30)
     If Not tempList.Contains(item) Then
            tempList.Add(item)
     End If
Next
IO.File.WriteAllLines("C:\download.txt", tempList.ToArray)

[cass84]

That wasn't exactly what I was looking for but I figured out the expression for a new column in the datatable that worked for me by joining the two columns together but it won't allow me to add the unique validation...

[stanav]

That wasn't exactly what I was looking for but I figured out the expression for a new column in the datatable that worked for me by joining the two columns together but it won't allow me to add the unique validation...

Well, then you have to tell us exactly what you're looking for, I guess
And BTW, you can't guarantee by combining 2 columns you will get a unique value, so you have to have some way to hold all the combined values you have and check each new combined value against that list (as I showed in the last post).

[cass84]

Sorry

I understand the use of the list, the only problem is I need someway to create a list & check it as data is entered rather than just checking the list when the file is being created...

Like if someone enters "01" into FieldA & "02" into FieldB & they click the save button but "0102" already exists in the table so it throws an exception....

I've tried so many ways

[stanav]

Well, in this case you just declare the list with class scope and you can refer to it anywhere in the class.

Private myList as New List(Of String)

And whenever you need to add an item to it, say, in a button click, you just check that item against the list and if it already there, you show a message to the user telling them so, esle you add it to the list.