当前位置：文江博客话题详情

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

发布于 2024-09-13 11:19:46 字数 394 浏览 1 评论 0原文

我时不时地会遇到一位同事开发的旧系统的问题。他们往往有数千行代码来完成简单的事情，例如导入 csv 文件。

目前的vba过程是：

打开excel应用程序
创建新工作表将
csv文件填充
到excel中将标题名称添加到文件
将工作表保存为新的excel文件
将文件导入到访问数据项目sql表中。
处理数据

我想要做的是：

将csv导入到表中（就像获取外部数据功能一样）
处理数据

我进行了快速搜索，但看不到任何将文件吸入表中的简单方法。

任何帮助将不胜感激。

谢谢

保罗

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

因为看清所以看轻 2024-09-20 11:19:47

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

有关详细信息，请参阅此 MSDN 链接：TransferText 方法 [Access 2003 VBA语言参考]

下面是一个示例 TransferText 命令，用于将 C:\SomeFolder\DataFile.csv 导入到名为 tblImport 的表中。最后一个参数 HasFieldNames 为 False，表示 CSV 文件不包含字段名称。

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

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

In many cases, the easiest way to import CSV is with the TransferText method.

Refer to this MSDN link for details: TransferText Method [Access 2003 VBA Language Reference]

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.

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

The SpecificationName parameter 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.)

回复收藏 0 原文

病女 2024-09-20 11:19:47

我找到了一种将整个 CSV 导入 access 的好方法。
我的任务是将三个 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

使用此设置运行代码后，我能够在一分多钟（约 62 秒）内导入 300MB 的文件，并且能够确保每列都有适当的数据类型和正确的索引（无需额外的步骤）。因此，通过这种方法，我能够将速度提高 7 到 9 倍，并且很容易知道数据是正确的。

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

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

我知道这可能有点啰嗦，但是在这个领域很少有可靠且有用的资源。我花了几乎一天的时间来减少选项并解决 VBA 混乱问题。我希望这可以帮助那些和我有同样问题的人。

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

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.

What I ended up doing was to create my own XML import export specification.

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.

*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).

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.

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.

回复收藏 0 原文

°如果伤别离去 2024-09-20 11:19:47

有一种更简单的方法来导入 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

您从 Select 更改为 INSERT INTO 与 SELECT 组合，就这样了。

您可以在注册表中的 \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text 项中执行一些设置：

格式：TabDelimited、CSVDelimited、Delimited(X)，其中 X=一些字符

FirstRowHasNames：0,1

字符集：OEM、ANSI

There is an easier way to import a CSV! You can use the 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

You change from a Select to an INSERT INTO combined with a SELECT and there you are.

There are some settings you can do in the registry, in the key \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text:

Format: TabDelimited, CSVDelimited, Delimited(X) where X=some char

FirstRowHasNames: 0,1

CharacterSet: OEM, ANSI

回复收藏 0 原文