vba 在 Access 中使用 ADO 导入 CSV 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10509406/
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
Using ADO in Access to import CSV data
提问by toolshed
So I navigated to the following MSDN Resource Pagethat addresses how to use ADO objects. My problem is that I cannot get it to work.
因此，我导航到以下MSDN 资源页面，其中介绍了如何使用 ADO 对象。我的问题是我无法让它工作。
What I am trying to do is open a CSV file and read it line-by-line, then create SQL INSERT statements to insert the records into an existing Table in Access 2010. I have tried to find an easier method of doing this, but this appears to be my only option. doing this with the included tools, but so far, I haven't had any luck.
我想要做的是打开一个 CSV 文件并逐行读取它，然后创建 SQL INSERT 语句将记录插入到 Access 2010 中的现有表中。我试图找到一种更简单的方法来做到这一点，但是这似乎是我唯一的选择。使用包含的工具执行此操作，但到目前为止，我还没有任何运气。
The main issue here is that I have CSV files with inconsistent headings. I want to import 5 files into the same table, but each file will be different depending on which fields contained data. Those fields with no data in them were ignored during the extract. This is why I can't use something like DoCmd.TransferText.
这里的主要问题是我有标题不一致的 CSV 文件。我想将 5 个文件导入同一个表中，但每个文件将根据包含数据的字段而有所不同。在提取过程中忽略了其中没有数据的那些字段。这就是为什么我不能使用像 DoCmd.TransferText 这样的东西。
So, now I need to create a script that will open the text file, read the headers in the first line and create a SQL INSERT statement dependent on the configuration of that particular file.
所以，现在我需要创建一个脚本来打开文本文件，读取第一行中的标题并根据该特定文件的配置创建一个 SQL INSERT 语句。
I have a feeling that I have a good handle on how to appraoch the issue, but no matter what I try, I can't seem to get things working using ADO.
我有一种感觉，我可以很好地处理如何解决这个问题，但无论我尝试什么，我似乎都无法使用 ADO 使事情正常工作。
Could anyone explain how I can achieve this? My sticking point is getting the Access DB to receive information from the CSV files via ADO.
谁能解释我如何实现这一目标？我的症结是让 Access DB 通过 ADO 从 CSV 文件接收信息。
回答by HansUp
Instead of reading the CSV file line-by-line, then doing something with each line, I think you should open the file as an ADO recordset. And open a DAO recordset for your Access destination table.
我认为您应该将文件作为 ADO 记录集打开，而不是逐行读取 CSV 文件，然后对每一行进行处理。并为您的 Access 目标表打开一个 DAO 记录集。
You can then iterate through the fields in each row of the ADO recordset and add their values into a new row of the DAO recordset. As long as the destination table includes fields with the same names and compatible data types as the CSV fields, this can be fairly smooth.
然后，您可以遍历 ADO 记录集每一行中的字段，并将它们的值添加到 DAO 记录集的新行中。只要目标表包含与 CSV 字段具有相同名称和兼容数据类型的字段，这就会相当顺利。
Public Sub Addikt()
#If ProjectStatus = "DEV" Then
' needs reference for Microsoft ActiveX Data Objects
Dim cn As ADODB.Connection
Dim fld As ADODB.Field
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
#Else ' assume PROD
Const adCmdText As Long = 1
Const adLockReadOnly As Long = 1
Const adOpenForwardOnly As Long = 0
Dim cn As Object
Dim fld As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
#End If
Const cstrDestination As String = "tblMaster"
Const cstrFile As String = "temp.csv"
Const cstrFolder As String = "C:\share\Access"
Dim db As DAO.Database
Dim rsDao As DAO.Recordset
Dim strConnectionString As String
Dim strName As String
Dim strSelect As String
strConnectionString = "Provider=" & _
CurrentProject.Connection.Provider & _
";Data Source=" & cstrFolder & Chr(92) & _
";Extended Properties='text;HDR=YES;FMT=Delimited'"
'Debug.Print strConnectionString
cn.Open strConnectionString
strSelect = "SELECT * FROM " & cstrFile
rs.Open strSelect, cn, adOpenForwardOnly, _
adLockReadOnly, adCmdText
Set db = CurrentDb
Set rsDao = db.OpenRecordset(cstrDestination, _
dbOpenTable, dbAppendOnly + dbFailOnError)
Do While Not rs.EOF
rsDao.AddNew
For Each fld In rs.Fields
strName = fld.Name
rsDao.Fields(strName) = rs.Fields(strName).value
Next fld
rsDao.Update
rs.MoveNext
Loop
rsDao.Close
Set rsDao = Nothing
Set db = Nothing
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
This is the Declarations section of the module where I saved that procedure:
这是我保存该过程的模块的声明部分：
Option Compare Database
Option Explicit
#Const ProjectStatus = "DEV" '"DEV" or "PROD"
Change the values for these constants to test it on your system:
更改这些常量的值以在您的系统上进行测试：
Const cstrDestination As String = "tblMaster"
Const cstrFile As String = "temp.csv"
Const cstrFolder As String = "C:\share\Access"
Note the folder name does not include a trailing backslash.
请注意，文件夹名称不包含尾部反斜杠。
You will want to adapt that procedure to pass the file name as a parameter instead of leaving it as a constant. And, if your CSV files are stored in more than one directory, you will want to pass the folder name as a parameter, too.
您将需要修改该过程以将文件名作为参数传递而不是将其保留为常量。而且，如果您的 CSV 文件存储在多个目录中，您也需要将文件夹名称作为参数传递。
Hopefully showing you how to do it for one file will be enough and you can then take it from here to handle all your CSV files. Also consider adding error handling.
希望向您展示如何为一个文件执行此操作就足够了，然后您可以从此处获取它来处理所有 CSV 文件。还可以考虑添加错误处理。
