Exporting all tables to comma delimited text files

<< Click to Display Table of Contents >>

Navigation:  Design and Tables  >

Exporting all tables to comma delimited text files

Access 2016

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


     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"


 Exit Sub


 ' 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.

downloadThe download file is called  robinson_exportcsv.accdb and can be downloaded from this page Design and Tables >>

Import both the form and the module into your database


Written by Garry Robinson


User Story from Years Ago
Exporting to text files means that, in all likelihood, you still will be able to read the files in 20 years—that's as long as CD-ROMs, tapes, or other such media will still be readable. Moreover, if you think that long-term recovery is unlikely, listen to this story. One project that I was involved with had a database of geological data that cost $50 million to put together. Another company bought the project, did nothing with the data for five years, and in the end couldn't read the backup tapes. When we were asked to help, we managed to recover the text data backups from our tapes, and these were used to build a reasonable copy of the database. We also recovered the database from tapes, but the format was proprietary, and the software that could read the proprietary format was long gone.



Another Article to Try is

Exporting and Recovering Programming Objects