Exporting all tables to comma delimited text files

<< Click to Display Table of Contents >>

Navigation:  Tables >

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

 

201201_gr4_1
Figure 1 - The form that unloads all tables to text

 

201201_gr4_2

Figure 2 - All the tables are unloaded to a text file

 

201201_gr4_3

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

 

We sell FMS Tools and bundle them with other discounted and free products

 

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.

 

201201_gr4_4

Figure 4 - Import a CSV file

 

201201_gr4_5

Figure 5 - Import a csv file

 

Choose delimited text and then make sure that You Choose

 

201201_gr4_6

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