vba 将 CSV 文件导入 MS Access 2010 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18394037/
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
Importing CSV file into MS Access 2010 Tables
提问by user1815586
I'm new into VBA coding, I am looking into creating an Import UI in ms access for user to import CSV files and insert into a new temp table, from the temp table there will be SQL query to split data into different tables and lastly drop the temp table.
我是 VBA 编码的新手，我正在考虑在 ms 访问中创建一个导入 UI，以便用户导入 CSV 文件并插入到新的临时表中，从临时表中将有 SQL 查询将数据拆分到不同的表中，最后删除临时表。
As per the codes, I have created the import button and coded the file picker. However I am lost on how to code it to import into a new temp table and follow by the SQL queries.
根据代码，我创建了导入按钮并对文件选择器进行了编码。但是，我不知道如何对其进行编码以导入新的临时表并遵循 SQL 查询。
Appreciate all helps
感谢所有帮助
Sub ImportButton_Click()
Dim strfilename As String
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select the CSV file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "CSV Files", "*.csv", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
strfilename = .SelectedItems(1)
DoCmd.TransferText TransferType:=acImportDelim, _
TableName:="Import_Table", FileName:=strfilename
Else
Exit Sub
End If
End With
End Sub
回答by Renaud Bompuis
Have a look at this article Working with external text files in MS Access, it should help you.
看看这篇文章在 MS Access 中使用外部文本文件，它应该对你有所帮助。
Basically you can create some schema for the text file and then query it using SQL, it as if it was a table in a database.
基本上，您可以为文本文件创建一些模式，然后使用 SQL 查询它，就好像它是数据库中的表一样。
From there you can easily go through the data using recordsets in VBA or INSERTqueries to create or fill your other tables.
从那里您可以轻松地使用 VBA 中的记录集或INSERT查询来浏览数据以创建或填充其他表。
回答by hoopzbarkley
Try
尝试
docmd.RunSQL("SELECT * INTO myTable FROM Import_Table WHERE [conditions]")
Replacing the [conditions] with your criteria and myTable with the name of the table you want to create.
将 [conditions] 替换为您的条件，并将 myTable 替换为您要创建的表的名称。
You would likely want to set
你可能想设置
DoCmd.SetWarnings False
first, to avoid users seeing record commit prompts. But it should be set back to True afterwards.
首先，避免用户看到记录提交提示。但是之后应该将其设置回 True 。
Alternatively, you could just create some Access "Make Table" queries (change query type on the Design tab of the ribbon) if you'd rather use the query designer.
或者，如果您更愿意使用查询设计器，您可以只创建一些 Access“制作表”查询（在功能区的“设计”选项卡上更改查询类型）。
