访问数据项目在VBA中导入CSV文件

Question

我偶尔会碰到一个我的同事开发的旧系统的问题。他们倾向于拥有数千行代码来执行简单的操作，例如导入csv文件。

目前VBA过程是：

• 打开Excel应用
• 创建新的工作表
• 填充CSV文件
• 到Excel中的头名添加到文件
• 保存工作表作为新的excel文件
• 将文件导入到访问数据项目sql表中。
• 处理数据

我想用它做的是：

• 导入CSV表（如获取外部数据功能）
• 过程中的数据

我有一个快速搜索，并不能看到任何简单的方法，只是吸吮文件到表中。

任何帮助，将不胜感激。

感谢

保罗

Answer 1

有导入CSV更省事！您可以使用Microsoft Text Odbc Driver。

Sub Import() 
    Dim conn as new ADODB.Connection 
    Dim rs as new ADODB.Recordset 
    Dim f as ADODB.field 

    conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\temp;" 
    rs.Open "SELECT * FROM [test.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText 

    While Not rs.EOF 
     For Each f In rs.Fields 
     Debug.Print f.name & "=" & f.Value 
     Next 
    Wend 
End Sub 

您从一个选择更改为一个INSERT INTO结合一个SELECT并且您在那里。

有一些设置，您可以在注册表中做，在关键\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text：

格式：TabDelimited，CSVDelimited，分隔（X），其中X =一些字符

FirstRowHasNames：0,1

字符集：OEM，ANSI

Answer 2

在许多情况下，导入CSV的最简单方法是使用TransferText方法。

请参阅本MSDN链接的详细信息：TransferText Method [Access 2003 VBA Language Reference]

这是TransferText命令导入℃的例子：\ SomeFolder \ DataFile.csv到一个名为tblImport表。最后一个参数HasFieldNames为False，表示CSV文件不包含字段名称。

DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", _ 
    "tblImport", "C:\SomeFolder\DataFile.csv", False 

的SpecificationName参数是可选。但是，通常可以通过创建自己的导入规范并在TransferText命令中包含其名称来获得更好的结果。该规范允许您确定将数据加载到哪些表字段，调整数据类型以及其他一些选项。当您手动导入数据文件时，您可以创建自己的导入规范...选择您的自定义导入选项，并将这些选项保存为命名规范。 （在导入向导对话框中查找“高级”按钮。）

Answer 3

BULK INSERT通常速度更快，它可以在X64机器上运行。上面提到的文本驱动程序可能无法在某些X64环境中使用。

我会推荐不使用格式文件，没有它会简单得多。

http://msdn.microsoft.com/en-us/library/ms188365.aspx

批量插入AdventureWorks2008R2.Sales.SalesOrderDetail FROM 'F：\订单\ lineitem.tbl' WITH （ FIELDTERMINATOR = '|'， ROWTERMINATOR = '| \ N' ）

Answer 4

我发现了一个很好的方法来将整个CSV导入访问。 我的任务是为一个数据库导入三个CSV到三个表中。这必须完成约100次，每个CSV的范围从200MB到500MB。由于三个表格模式对于每个数据库都是相同的，所以我花了一些时间试图找到创建脚本以便为我导入所有这些脚本的最佳方法。 我第一次使用

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, p1, _ 
Application.CurrentProject.Path & "\Page1\_8_lift_base_" & dbName & ".csv",_ 
True, sh.Name & "!" 

这在打开CSV工作了大多数情况下，除了偶尔有“只读”提示符出现，并停止进口，直到它被关闭。另外，一个300MB的CSV需要8到10分钟左右的时间。对于100个DB，这是不可接受的。

我最终做的是创建我自己的XML导入导出规范。

Sub make_import_spec(filePath As String, tableName As String, pageNum As Long) 
'By Ryan Griffin 
Dim name_of_spec As String 
name_of_spec = "imspec" & tableName 
Dim xml As String 
'This xml string contains the specifications the use for that table 
xml = "" 
xml = xml & "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf 
xml = xml & "<ImportExportSpecification Path=" & Chr(34) & filePath & Chr(34) & " xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbCrLf 
xml = xml & " <ImportText TextFormat=""Delimited"" FirstRowHasNames=""true"" FieldDelimiter="","" CodePage=""437"" Destination=" & Chr(34) & tableName & Chr(34) & " >" & vbCrLf 
xml = xml & "  <DateFormat DateOrder=""MDY"" DateDelimiter=""/"" TimeDelimiter="":"" FourYearDates=""true"" DatesLeadingZeros=""false"" />" & vbCrLf 
xml = xml & "  <NumberFormat DecimalSymbol=""."" />" & vbCrLf 
xml = xml & "   <Columns PrimaryKey=""{none}"">" & vbCrLf 
xml = xml & "     <Column Name=""Col1"" FieldName=""field1"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Text"" Width=""12"" />" & vbCrLf 
xml = xml & "     <Column Name=""Col2"" FieldName=""field2"" Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" Width=""16"" />" & vbCrLf 
xml = xml & "     <Column Name=""Col3"" FieldName=""field3"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Text"" Width=""24"" />" & vbCrLf 
xml = xml & "     <Column Name=""Col4"" FieldName=""field4"" Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" Width=""16"" />" & vbCrLf 
xml = xml & "   </Columns>" & vbCrLf 
xml = xml & "  </ImportText>" & vbCrLf 
xml = xml & "</ImportExportSpecification>" 
'By Ryan Griffin 
'Now you can add the specification to the project 
CurrentProject.ImportExportSpecifications.Add name_of_spec, xml 
' This will run your specification and import you csv file 
DoCmd.RunSavedImportExport name_of_spec 
End Sub 

与此设置，我能够导入一个300MB的文件，在短短一分钟内（〜62秒），并能够确保每列有相应的数据类型，且正确的索引上运行的代码（无后额外的步骤）。所以采用这种方法，我能够在7到9倍的速度增加之间实现一些，并且容易知道数据是正确的。

*注意：对于此函数，我提供了CSV文件路径（其中包括name.csv），所需的表名和pagenum，它是表引用。 （我用它来区分表，在xml字符串中，我有一个基于pageNum的if语句，其中pageNum为1;将这些列添加到字符串中）。

只要csv文件不包含“。”，这对于您所有的CSV导入需求都能很好地工作。 （期间）名称[除了扩展名]。为此，您需要使用脚本FileSystemObject来获取文件，并在导入之前将其名称更改为使用类似于下划线而不是句点的名称。

我知道这可能有点长，但是在这方面可靠和有用的资源非常少。花了我差不多一天的时间来减少选项并理清VBA混乱。我希望这能帮助那些和我一样麻烦的人。