将所有Microsoft Access表导出到单独的CSV文件中

Question

我得到了一个有200个表的.mdb。我想写一个vba例程，将数据导出到单独的CSV中。

在谷歌上我发现了这个代码：

Public Sub ExportAllTablesToCSV()
Dim i As Integer
Dim name As String

For i = 0 To CurrentDb.TableDefs.Count
name = CurrentDb.TableDefs(i).name

If Not Left(name, 4) = "msys" And Not Left(name, 1) = "~" Then
DoCmd.TransferText acExportDelim, "", name, _
"c:exports" & name & ".csv", _
True
End If

Next i
End Sub

这看起来还可以，但它想要一个导出规范(acExportDelim之后的参数(，这使得脚本变得无用，因为我不想手动创建200导出规范。

知道吗？

Answer 1

这对我有效。

Option Compare Database
Option Explicit

Private Sub Command0_Click()
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Set db = CurrentDb()

sExportLocation = "C:all_files"
For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation & "Table_" & td.Name & ".csv", True
End If
Next td
Set db = Nothing
Set c = Nothing
MsgBox "All database objects have been exported as a csv file to " & sExportLocation, vbInformation
Exit_ExportDatabaseObjects:
Exit Sub
Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ExportDatabaseObjects

End Sub

Answer 2

您确定要CSV选项吗？由于使用此格式，您无法将表导入回ms访问(因为没有保存数据模式(，因此需要200个具有模式的文件。好的，如果csv-有一个页面https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transfertext.我，我尝试了其他更方便的XML方法。如果你想将表保存为可转换格式

Dim db As DAO.Database
Dim td As DAO.TableDefs
Set db = CurrentDb()
Set td = db.TableDefs
For Each t In td    'loop through all the fields of the tables
'Ignore any system tables
If Left(t.Name, 4) = "MSys" Then GoTo Continue
DoCmd.OutputTo acOutputTable, t.Name, , _
(strPathAndFileName & t.Name & ".xml"), False   
Continue:
Next

`系统将提示您选择任何带有格式确认的t格式和模式

所以你必须用键或鼠标点击3 x 200次，然后在大约10分钟内完成工作。