在 VBA 中访问数据项目导入 CSV 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3413740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Data Project Importing CSV File In VBA
提问by Paul
Every now and then I come across a problem with an old system one of my colleagues has developed. They tend to have thousands of lines of code to do a simple thing like importing a csv file.
我时不时地遇到我的一位同事开发的旧系统的问题。他们往往有数千行代码来做一个简单的事情，比如导入一个 csv 文件。
Currently the vba process is:
目前vba进程是：
- open excel application
- create new worksheet
- populate the csv file
- into excel add the header names to the file
- save the worksheet as a new excel file
- imports the file into the access data project sql table.
- Process the data
- 打开 Excel 应用程序
- 创建新工作表
- 填充 csv 文件
- into excel 将标题名称添加到文件中
- 将工作表另存为新的 excel 文件
- 将文件导入到访问数据项目 sql 表中。
- 处理数据
What I want to do with it is:
我想用它做的是：
- import the csv to the table (Like the get external data function)
- process the data
- 将 csv 导入表（如获取外部数据功能）
- 处理数据
I have had a quick search and cannot see any easy methods of just sucking the file into the table.
我进行了快速搜索，但看不到任何将文件吸入表格的简单方法。
Any help would be appreciated.
任何帮助，将不胜感激。
Thanks
谢谢
Paul
保罗
采纳答案by dwo
There is an easier way to import a CSV! You can use the Microsoft Text Odbc Driver.
有一种更简单的方法来导入 CSV！您可以使用 Microsoft Text Odbc 驱动程序。
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
You change from a Select to an INSERT INTO combined with a SELECT and there you are.
您从 Select 更改为 INSERT INTO 与 SELECT 相结合，然后就可以了。
There are some settings you can do in the registry, in the key \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text:
您可以在注册表中进行一些设置，在键中\\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text：
Format: TabDelimited, CSVDelimited, Delimited(X) where X=some char
格式：TabDelimited、CSVDelimited、Delimited(X) 其中 X=some char
FirstRowHasNames: 0,1
FirstRowHasNames: 0,1
CharacterSet: OEM, ANSI
字符集：OEM、ANSI
回答by HansUp
In many cases, the easiest way to import CSV is with the TransferText method.
在许多情况下，导入 CSV 的最简单方法是使用 TransferText 方法。
Refer to this MSDN link for details: TransferText Method [Access 2003 VBA Language Reference]
有关详细信息，请参阅此 MSDN 链接：TransferText 方法 [Access 2003 VBA 语言参考]
Here is an example TransferText command to import C:\SomeFolder\DataFile.csv into a table named tblImport. The last parameter, HasFieldNames, is False to indicate the CSV file doesn't include field names.
下面是一个示例 TransferText 命令，用于将 C:\SomeFolder\DataFile.csv 导入名为 tblImport 的表中。最后一个参数 HasFieldNames 为 False，表示 CSV 文件不包含字段名称。
DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", _
"tblImport", "C:\SomeFolder\DataFile.csv", False
The SpecificationNameparameter is optional. However, you can often get better results by creating your own import specification and including its name in the TransferText command. The specification allows you to identify which table fields to load the data into, adjust data types, and a whole host of other options. You can create your own import specification when you manually import your data file ... select your custom import options, and save those choices as a named specification. (Look for an "Advanced" button on the Import Wizard dialog.)
该SpecificationName参数是可选。但是，您通常可以通过创建自己的导入规范并将其名称包含在 TransferText 命令中来获得更好的结果。该规范允许您确定要将数据加载到哪些表字段、调整数据类型以及大量其他选项。您可以在手动导入数据文件时创建自己的导入规范……选择自定义导入选项，并将这些选择保存为命名规范。（在“导入向导”对话框中查找“高级”按钮。）
回答by Ryan G
I have found a nifty way to import entire CSVs into access. I was tasked with importing three CSVs into three tables for a single database. This had to be done about 100 times, and each CSV would range from 200MB to 500MB. Since three table schemas were the same for each database I spent some time trying to find the best way to create a script to import all of these for me. I first used
我找到了一种将整个 CSV 导入访问的好方法。我的任务是将三个 CSV 导入单个数据库的三个表中。这必须执行大约 100 次，每个 CSV 的范围从 200MB 到 500MB。由于每个数据库的三个表模式都相同，我花了一些时间试图找到创建脚本的最佳方式来为我导入所有这些。我第一次使用
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, p1, _
Application.CurrentProject.Path & "\Page1\_8_lift_base_" & dbName & ".csv",_
True, sh.Name & "!"
This worked for most cases, except occasionally upon opening the CSV a "read only" prompt would appear, and halt the imports until it was closed. Also, a 300MB CSV would take somewhere around 8 to 10 minutes. For 100 DBs, this is not acceptable.
这适用于大多数情况，除了偶尔在打开 CSV 时会出现“只读”提示，并在关闭之前停止导入。此外，一个 300MB 的 CSV 大约需要 8 到 10 分钟。对于 100 个 DB，这是不可接受的。
What I ended up doing was to create my own XML import export specification.
我最终做的是创建自己的 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
After running the code with this setup I was able to import a 300MB file in just over a minute (~62seconds), and was able to make sure every column had the appropriate dataType and correct indexing (without an extra step). So with this method I was able to achieve some where between a 7 to 9 times speed increase, with the ease of knowing that the data will be correct.
在使用此设置运行代码后，我能够在一分钟多一点（约 62 秒）内导入一个 300MB 的文件，并且能够确保每列都具有适当的数据类型和正确的索引（无需额外步骤）。因此，通过这种方法，我能够将速度提高 7 到 9 倍，并且很容易知道数据是正确的。
*Note: for this function I am providing the CSV file path (which includes the name.csv), the desired tablename, and pagenum, which is the table reference. (I used this to distinguish between the tables. In the xml string, I had an if statement based on that pageNum, where if pageNum was 1; add these columns to the string).
*注意：对于这个函数，我提供了 CSV 文件路径（包括 name.csv）、所需的表名和 pagenum，这是表引用。（我用它来区分表。在 xml 字符串中，我有一个基于该 pageNum 的 if 语句，其中如果 pageNum 为 1；将这些列添加到字符串中）。
This will work beautifully for all your CSV importing desires, so long as the csv files do not include a "." (period) in the name [besides the extension]. For this you will need to use a Scripting FileSystemObject to get the file, and change its' name to use something like an underscore rather than a period, before importing.
只要 csv 文件不包含“.”，这将非常适合您所有的 CSV 导入需求。（句点）在名称中[除了扩展名]。为此，您需要使用 Scripting FileSystemObject 来获取文件，并在导入之前更改其名称以使用下划线而不是句点之类的名称。
I know it may be a little long winded, but there are very few resources out there that are reliable and useful in this area. Took me a almost a day to whittle down the options and sort out the VBA mess. I hope this can help anybody out there who is having the same trouble that I was.
我知道这可能有点啰嗦，但是在这方面可靠且有用的资源很少。我花了将近一天的时间来减少选项并理清 VBA 的混乱。我希望这可以帮助那些和我有同样问题的人。
回答by Aaron Kempf
BULK INSERT is generally faster, and it will work on an X64 machine. The text driver mentioned above will probably not work in some X64 environments.
BULK INSERT 通常更快，并且可以在 X64 机器上运行。上面提到的文本驱动程序可能在某些 X64 环境中不起作用。
I would reccomend NOT using the format file, it's a lot simpler without it.
我建议不要使用格式文件，没有它会简单得多。
http://msdn.microsoft.com/en-us/library/ms188365.aspx
http://msdn.microsoft.com/en-us/library/ms188365.aspx
BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR =' |', ROWTERMINATOR =' |\n' )
批量插入 AdventureWorks2008R2.Sales.SalesOrderDetail FROM 'f:\orders\lineitem.tbl' WITH ( FIELDTERMINATOR =' |', ROWTERMINATOR =' |\n' )
