Exporting all tables to comma delimited text files |
  
|
Exporting all tables to comma delimited text files |
|
Access 2010/2007
Use the following example to export all the tables in a database to a comma-delimited text format like that shown in Figure 3. You will find this sample under the only button in the details section of the download form called frm_unloadAll. The sample works by first establishing the export folder. In this case, it will create a subfolder called Unload directly under the folder where the database is located. Then a DAO TableDef collection is established, and a loop is used to cycle through all the data tables in the collection. The TransferToText method then exports the table to comma-delimited format. If you open one of the comma-delimited files, it may display in either a text editor or Excel, depending on your file type associations in Windows Explorer. See also why this is important in working out the size of tables in your database
Figure 1 - The form that unloads all tables to text
Figure 2 - All the tables are unloaded to a text file
Figure 3 - Comma delimited csv file produced by the download
The following code sample exports all the tables in a database to a comma-delimited text format. This can be found under the button in Figure 1.
Private Sub unload_all_Click()
' This form requires a reference to
' Microsoft Office 12 Database Engine Object library.
Dim i As Integer, unloadOK As Integer
Dim MyTable As DAO.TableDef
Dim MyDB As DAO.Database, MyRecords As DAO.RecordSet
Dim filen As String, unloadDir As String
' See Microsoft Knowledge Base Article 306144 if you want to
' change the following file type.
Const UNLFILETYPE = ".csv"
Const UNLSUBFOLDER = "unload\"
On Error GoTo unload_all_Failed
unloadDir = GetDBPath_FX & UNLSUBFOLDER
Set MyDB = CurrentDb
If Len(Dir(unloadDir, vbDirectory)) = 0 Then
unloadOK = MsgBox("All tables will be unloaded to a new directory called " & _
unloadDir, vbOKCancel, "Confirm The Unload Directory")
If unloadOK = vbOK Then
MkDir unloadDir
Else
GoTo unload_all_Final
End If
End If
' Loop through all tables, extracting the names.
For i = 0 To MyDB.TableDefs.Count - 1
Set MyTable = MyDB.TableDefs(i)
' Create the file name as a combination of the table name and the file type.
filen = unloadDir & MyTable.Name & UNLFILETYPE
If left(MyTable.Name, 4) <> "Msys" And left(MyTable.Name, 1) <> "~" Then
' Not an Access system table.
'Export data in comma-delimited format with column headers.
DoCmd.Echo True, "Exporting table " & MyTable.Name & " to " & filen
DoCmd.TransferText A_EXPORTDELIM, , MyTable.Name, filen, True
End If
Next i
MsgBox "Unloaded all tables to ... " & unloadDir, 64, "Unloaded Tables"
unload_all_Final:
Exit Sub
unload_all_Failed:
' Problems with unloading.
Select Case Err.Number
Case Else
MsgBox "Error number " & Err.Number & " -> " & Err.Description, _
vbCritical, "Problem unloading tables"
End Select
Resume unload_all_Final:
End Sub
Recovering Data From a Comma-Delimited Text File
Create a new blank database
On the External Data ribbon, Choose Import Text as in Figure 4 and then Import as in Figure 5.
Figure 4 - Import a CSV file
Figure 5 - Import a csv file
Choose delimited text and then make sure that You Choose
Figure 6 - Make sure you import column headers
Then follow the Wizard till the end and on the last window, click Finish.
Now that you have the data loaded into a database, you can append, merge, or replace existing data tables as you see fit. Choosing whether to append, merge, or replace data would be specific to your own data structures and is not within the scope of this book.
Comma-delimited files can prove troublesome if you use them to recover from long-term storage if they are stored without documentation. To alleviate this risk, I recommend that you also store information about the structure of the data in the same location as the text files. Maybe even save a picture of your relationship diagram to help with the recovery of the files.
Download
The download file for this article is here - Import both the form and the module into your database and add a reference in the in the VBA project to
"Microsoft Office 12 Access Database Engine Objects"
Written by Garry Robinson
User Story from Years Ago