Export all tables in an Access database to CSV
I don’t usually mess around with Access much, but when I do it’s usually to have it act as an intermediary data source while I’m getting data from one format to another.
I had just such a problem to solve today, and the usually excellent out-of-the-box import/export processes let me down a little. Essentially I needed to get the data from each of the tables into separate CSV files - unfortunately I had quite a few tables to do and the UI only allows you to do one at a time.
Here’s the little VBA routine I put together to solve the problem:
1Public Sub ExportAllTablesToCSV() 2 3 Dim i As Integer 4 Dim name As String 5 6 For i = 0 To CurrentDb.TableDefs.Count 7 name = CurrentDb.TableDefs(i).name 8 9 If Not Left(name, 4) = "msys" And Not Left(name, 1) = "~" Then 10 DoCmd.TransferText acExportDelim, , name, _ 11 "c:\exports\" & name & ".csv", _ 12 True 13 End If 14 15 Next i 16 17End Sub