Output to comma delimited text file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Avi - 17 Apr 2006 16:53 GMT

I need to create a text file that has the data from the 10 tables in
the database. The number of fields in the tables exceeds 255 and so I
cannot make a new table with all the fields and then export it into a
text file.
Is there any s/w out there I could use? I am not much of a programmer
but I heard I could use VBA to get this done. Any help with the code
will be appreciated.
Thanks

Reply to this Message

Rich P - 17 Apr 2006 18:41 GMT

your post is not completely clear, but I will give it a shot. I
understand that you want to write data from 10 tables to 1 text file.
What is not clear is if the tables all have 255 fields or not. The
other issue is that data contained in each field. You want a comma
delimited text file. If there is any punctuation in any of the fields,
and that punctuation happens to be a comma, it will throw off your
delimiter (the comma) because you will have 2 commas for that field.
Lets say all the tables have 255 fields and there are no commas in any
of the fields (actually, it doesn't really matter if the tables all have
the same number of fields for writing to a text file - but reading from
the text file to a table might make a difference). Then you can do this
using VBA. The following code assumes you are writing data from an mdb
file:

Sub WriteDataToTextFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, strPath As String, arrTbls As Variant
Dim i As Integer, j As Integer

strPath = CurrentProject.Path
arrTbls = Array("tbl1", "tbl2", "tbl3", ..., "tbl10")
Set DB = CurrentDB
Close #1
Open strPath & "\yourTxtFile.txt" For Append As #1
For i = 0 to Ubound(arrTbls)
Set RS = DB.OpenRecordset(arrTbls(i))
Do While Not RS.EOF
str1 = ""
For j = 0 to RS.Fields.Count - 2
str1 = str1 & RS(j) & ","
Next
str1 = str1 & RS(j)
Print #1, str1
RS.MoveNext
Loop
RS.Close
Next
Close #1
End Sub

So what is going on here is that I am placing all the table names in an
array called arrTbls and looping through that array. Then I use DAO
code to loop through the records in each table. The part that writes to
the textfile is

Open strPath & "\yourTxtFile.txt" For Append As #1

and in the Do Loop I collect the values of all the fields in a string
variable called str1 using yet another For Loop, and then I use the
Print function to print that row to the text file, then move on to the
next record.

You can copy/paste this code in a standard code module. Then you can
place your mouse cursor over some of the expressions like Open (from the
Open strpath line) and press the F1 key to bring up a Help file that
explains how the Open statemenet works. You can place your mouse cursor
over most VBA objects (not the variables that you declare) and the
properties or methods and press the F1 key to bring up a Help file that
provides some explanation on how the object works.

Rich

Reply to this Message

Avi - 17 Apr 2006 19:02 GMT

Thanks for the info Rich.
Heres more info:
1. There are 30-60 fields in each table
2. I made a mistake while posting. We need a tab delimited text file
instead of comma so I guess we dont have to worry about the comma in
the text.
3. I just noticed that I might need to eliminate some of the fields in
the tables. I guess I could update the code you provided and read one
table at a time and eliminate the field using the if statement. (or
maybe use the array number for the table and use it to eliminate the
fields. I guess I will have to eliminate the first 2 fields most of the
time)
4. One quick question. To add a tab between the fields do i use a tab
within quotes (like u did for a comma) or is there any other way to do
it.

Thanks

Reply to this Message

Rich P - 17 Apr 2006 19:23 GMT

You might be better off creating a separate text file for each table for
now until you get the hang of what is going on. Here is a modified
version of the first example routine. This routine only does one table.
So all you need to do is to change the name of the table and the name of
the textFile (so that you don't over write the textfile). And for the
delimiter, I changed it from "," to vbTab, a built-in VBA constant for
tab (could also use chr(9))

Sub WriteDataToTextFile()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, strPath As String
Dim i As Integer, j As Integer

strPath = CurrentProject.Path
Set DB = CurrentDB
Set RS = DB.OpenRecordset("tbl1")
Close #1
Open strPath & "\txtFile1.txt" For Append As #1
Do While Not RS.EOF
str1 = ""
For j = 0 to RS.Fields.Count - 2
str1 = str1 & RS(j) & vbTab
Next
str1 = str1 & RS(j)
Print #1, str1
RS.MoveNext
Loop
RS.Close
Close #1
End Sub

You can run this subroutine after your paste it into a Standard Code
module (not a Form code module) by pressing the F5 Key. After you run
the routine, go to windows explorer and check to see if your textfile
exists and contains your table data. Then you can rerun the routine by
changing the Table and textfile names:

Set RS = DB.OpenRecordset("tbl2") '<--change tbl name here
Close #1
'--change textfile name from "\txtFile1.txt" to
'--"\txtFile2.txt" for example
Open strPath & "\txtFile2.txt" For Append As #1

Then rerun the routine
Rich

Reply to this Message