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
   9.             
   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 UtterAccess.com showed me the light. This code now gives column headings with the TRUE argument.  For more details, check out http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1752140

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.

 

 

—-