Economics should be open

December 2, 2008

MS Access Tables to Stata, for Residential Energy Consumption Survey

Problem: To convert MS Access tables (of the EIA Residential Energy Consumption Survey 1997 data) to CSV files for STATA import

This is also a general script for converting MS Tables to CSVs

Solution: Wrote a VBA script


  1. Open MS Access
  2. Tools | Macros | Visual Basic Editor
  3. Create a new module on your database by doing: Right Clickon database | Insert | Module
  4. Copy the following text (without the line numbers)
    1. Sub ExportAllTablesCsv()
    2. Dim dbMyDB As Database
    3. Set dbMyDB = OpenDatabase(“recs97_converted.mdb”)
    4.     For Each tdfCurrent In dbMyDB.TableDefs
    5.         fileoutname = “C:\” & tdfCurrent.Name & “.csv”
    6.         If Left(tdfCurrent.Name, 2) <> “MS” Then
    7.             DoCmd.TransferText acExportDelim, , tdfCurrent.Name, fileoutname, TRUE
    8.         End If
    10.     Next
    11. End Sub
  5. For your customization:
    1. change your mdb file to your mdb file
    2. The If statement (line 10) is there to deal with the fact that there are certain system tables that I needed to skip. None of my tables started with “MS”, so this was a simple non-general fix.
    3. If you look up help for TransferText it has a SpecificationName. I left that argument blank. You can change to tabs or other things using that.
    4. I had trouble with getting column headings, but jwhite at showed me the light. This code now gives column headings with the TRUE argument.  For more details, check out

If you can think of a better solution, I’d love your thoughts!

Other possible/failed solutions:

  • I tried running a query that joined all the tables with the EIA ID Num as the key for each table. Though this was easy, I got the “too many fields” error.
  • I think there is a way to use SQL (or something???) to select all the tables and write them and to import those into another program. STATA, to my limited knowledge, doesn’t do SQL. 
  • STATA supposedly does XML. One can use TransferText to write XML too. I don’t know XML well enough to try it.

Any comment appreciated.




Repeat of VBA code for easy copy/paste

Sub ExportAllTablesCsv()

Dim dbMyDB As Database

Set dbMyDB = OpenDatabase("recs97_converted.mdb")

    For Each tdfCurrent In dbMyDB.TableDefs
        fileoutname = "C:\" & tdfCurrent.Name & ".csv"
        If Left(tdfCurrent.Name, 2) <> "MS" Then
            DoCmd.TransferText acExportDelim, , tdfCurrent.Name, fileoutname, TRUE
        End If

End Sub

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: