Batch export

B

blindsey

I have an Access database with over 200 files. I'd like to export all
of them to csv files.

The Export function only handles one table at a time.

Any reasonably easy way I can export all of them?
 
D

Douglas J. Steele

It's not clear to me what you mean by "over 200 files" since an Access
database is a single mdb file. Do you mean over 200 tables?

You could use VBA to export them.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strFolder As String

strFolder = "C:\Some Folder\Some Subfolder\"

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
DoCmd.TransferText acExportDelim, , tdfCurr.Name, _
strFolderfilename & tdfCurr.Name & ".csv", True
End If
Next tdfCUrr
 
J

John W. Vinson

I have an Access database with over 200 files. I'd like to export all
of them to csv files.
Click to expand...

Databases don't contain "files" - but tables. Are all these tables of the same
structure?? How did you get in this mess, in other words?
The Export function only handles one table at a time.

Any reasonably easy way I can export all of them?
Click to expand...

Write VBA code and use the TransferText method to export them. You can loop
through the Tabledefs collection:

Public Sub ExportAll()
Dim tdf As DAO.Tabledef
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.Tabledefs
If Left(tdf.Name, 4) <> "MSys" Then ' exclude system tables
DoCmd.TransferText acExportDelim, , tdf.Name, _
"E:/SomePath/" & tdf.Name & ".txt"
Next tdf
End Sub

will export all non-system tables to the folder SomePath on drive E, with the
filename being the tablename with a .txt extension. See the online help for
TransferText for (quite a few) options.

John W. Vinson [MVP]
 
B

blindsey

Databases don't contain "files" - but tables.
Click to expand...

Duh. Sorry, brain fart. I was thinking of the output I was hoping
for ...
Are all these tables of the same
structure?? How did you get in this mess, in other words?
Click to expand...

It's someone's legacy data I'm converting into another system.
Write VBA code and use the TransferText method to export them. You can loop
through the Tabledefs collection:

Public Sub ExportAll()
Dim tdf As DAO.Tabledef
Dim db As DAO.Database
Set db = CurrentDb
For Each tdf In db.Tabledefs
If Left(tdf.Name, 4) <> "MSys" Then ' exclude system tables
DoCmd.TransferText acExportDelim, , tdf.Name, _
"E:/SomePath/" & tdf.Name & ".txt"
Next tdf
End Sub

will export all non-system tables to the folder SomePath on drive E, with the
filename being the tablename with a .txt extension. See the online help for
TransferText for (quite a few) options.

John W. Vinson [MVP]
Click to expand...

OK, thanks!
 
Top