Export to Tab delimited text file

  • Thread starter Thread starter Sierk
  • Start date Start date
S

Sierk

I am working with Access 2000 and I am creating a export routine to batch
export all the tables in the database to tab delimited text files with field
headers. I keep seeing that people are using TransferText, however this
requires Export parameter or schema.ini file for each of the tables to be
exported. This of course will be a major pain. Is there a way to batch
export MS Access tables to tab delimited text files while only specifying the
basics, ie table name, output file name, field delimiter type, text delimiter
type, Field names included?

Thanks
 
If the table structures are the same, then of course using a export spec
will do the trick....

Simply do one export manually, and when in the export wizard click on the
advanced tab......save your export with a name.
Then, you simply go:

docmd.TransferText acExportDelim, "mySpec"
,"tableorQuery","c:\output\t.txt",true

Where "mySpec" is the name of the export specifiation you saved during the
export.

If all of the tables are differnt, then I would consider rolling your own
export, or even better is to use the text export class here:

http://www.mvps.org/access/modules/mdl0058.htm
 
You could create the export spec for one manual export and save it with a
simple name like MyExportDelim.
You could use the same export spec for each table.
If you are trying to create an export spec on the fly for unknown tables,
this would be much more involved.

Jeanette Cunningham
 
Visitbazaar.com is a secure online shopping mall where you can shop over 200
stores and save up to 60% and more with coupons, deals and discounts. Since
October of 2007, we have helped many customers save money on products &
services ranging from arts and crafts to web services. At Visitbazaar.com,
we are strongly committed to keeping our online mall a safe place to shop
at, and to help our customers save money on a wide selection of products &
services.

My Window is a browser application which increases the internet browsing
speed by applying various compression techniques and protocol optimization.
Reduces the upload and download time in FTP. To switch on MyWindow feature,
log on Visitbazaar.com now.

http://www.visitbazaar.com/mywindow.html


Regards
Visitbazaar Info
 
Hi Sierk,

This is such a common issue - I thought I'd write a skeleton function to do
it.

There's a way, it's called VBA! :-)

Remarks and the assumptions - tsv or tab delimited file I will assume a tab
is going to be our field delimiter. Text happens to be a type 10 field. Now,
we hope/assume there aren't any humongous memo fields here, right? But just
in case, maybe we better test for a type 12 and skip over it if we find one.
The most common text delimiters would be the single quote chr(39) or double
quotes chr(34). I tend to favor the single quote but either way works just
fine as long as the user doesn't embed on into a field's contents. There are
some other field types that could ruin our export as well, but I assume if
you have a table that contains blobs or whatever, you know you can't export
that column to a tsv file. Create a select query (and save it), one that
doesn't include the 'no go' columns and export that recordset instead.

If this was to be bulletproof - i.e. someone was paying me a chunk of money
to make this a seamless, very clean export - I might have to search for and
substitute 'safe chars' in the place of 'bad chars' like comma's or quotes
or various brackets or slashes or pipe symbols - how much scrubbing is
needed depends on how you going to use the exported file. RegEx offers
superb ways to handle the scrubbing but RegEx is not the tool us
double-digit IQ types readily scribble out functions for. I have to RTFM
everytime I want use RegEx to parse some string data.

The rather spartan and unprotected quoted below code works... all you have
to do is pass it the complete path & file name, and the table name, and
don't mess up (don't send it a non-existent drive designation or a folder
name that doesn't exist or an illegal file name or a bad/wrong table name).
What programmers get paid the big bucks for is wiping the users little noses
for them and protecting them from paying the price for their bad data
inputs. I'm sure it will wrap badly in the news group but if you copy and
paste it into notepad, with wordwrap turned off, it will probably be easy to
straighten it out (for the most part).

I'm using the ancient QuickBasic style output to file as # instead of the
filescriptingobject. The filescriptingobject works and is probably more
efficient but it requires a reference to the appropriate scripting library
and some security minded folks get nervous about letting that library get
loaded.

If you want to, you could enumerate the tables collection using the
tabledefs object and export each and every table - using the tablename as
the filename and add the .tsv extension to it.

==============================
Function myExport(strFile As String, strTable As String) As Boolean


Dim rs As DAO.Recordset, f As Variant, i As Integer
Dim fNum As Integer, myRow As String, EndOfRow As Integer
fNum = FreeFile()
i = 0
Set rs = CurrentDb.OpenRecordset(strTable)

EndOfRow = rs.Fields.Count

If rs.RecordCount > 0 Then

'we have records, lets open up our target file
' and get the header labels

Open strFile For Output As #fNum

For Each f In rs.Fields
i = i + 1
If i < EndOfRow Then
myRow = myRow & Chr(39) & f.Name & Chr(39) & vbTab
Else
myRow = myRow & Chr(39) & f.Name & Chr(39)
End If
Next f

Print #fNum, myRow
rs.MoveFirst
Do While Not rs.EOF
i = 0
myRow = ""
For Each f In rs.Fields
Select Case rs.Fields(i).Type

Case Is = 12
'ut oh - we have a memo, could insert null
'or a space but I'm just tabbing past
If (i + 1) < EndOfRow Then
myRow = myRow & vbTab
End If

Case Is = 10
'we have text
If i + 1 < EndOfRow Then
myRow = myRow & Chr(39) & f.Value & Chr(39) &
vbTab
Else
myRow = myRow & Chr(39) & f.Value & Chr(39)
End If

Case Else
'we have numeric
If i + 1 < EndOfRow Then
myRow = myRow & f.Value & vbTab
Else
myRow = myRow & f.Value
End If
i = i + 1

End Select

Next f

Print #fNum, myRow
rs.MoveNext

Loop

End If

Close #fNum
Set rs = Nothing

'should write some error trapping stuff
'and return false if the export failed.
'along with bringing up a msgbox to show the err.no
'and err.description

myExport = True

End Function

Hope this helps you - (or someone)..

Gordon
 
Back
Top