在Access中使用ADO导入CSV数据 [英] Using ADO in Access to import CSV data

问题描述

因此，我浏览了以下 MSDN资源页，使用ADO对象。我的问题是，我不能让它工作。

我想做的是打开一个CSV文件，并逐行阅读，然后创建SQL INSERT语句将记录插入到Access 2010的现有表中。我试图找到一个更容易的方法，但这似乎是我唯一的选择。

这里的主要问题是，我有CSV文件具有不一致的标题。我想导入5个文件到同一个表，但每个文件将是不同的，取决于哪些字段包含数据。提取期间忽略其中没有数据的那些字段。这是为什么我不能使用像DoCmd.TransferText。

所以，现在我需要创建一个脚本，将打开文本文件，读取第一行，并创建一个依赖于该特定文件的配置的SQL INSERT语句。

我有一个感觉，我有一个很好的处理问题的方法，无论我尝试，我似乎不能使用ADO工作。

任何人都可以解释我可以实现这一点吗？我的关注点是让Access数据库通过ADO从CSV文件接收信息。

解决方案

而不是读取CSV文件行然后对每行做一些事情，我想你应该打开该文件作为ADO记录集。并为您的Access目标表打开一个DAO记录集。

然后，您可以遍历ADO记录集的每一行中的字段，并将它们的值添加到DAO记录集的新行中。只要目标表包含与CSV字段具有相同名称和兼容数据类型的字段，就可以非常顺利。

  Public Sub Addikt（）
 #If ProjectStatus =DEV然后
'需要Microsoft ActiveX数据对象的引用
 Dim cn As ADODB.Connection 
 Dim fld As ADODB.Field 
 Dim rs As ADODB.Recordset 
设置cn =新ADODB.Connection 
设置rs =新ADODB.Recordset 
 #Else'假设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）
设置rs = CreateObject（ADODB.Recordset）
 #End如果
 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）& _ 
;扩展属性='text; HDR = YES; FMT = Delimited'
'Debug.Print strConnectionString 
 cn.Open strConnectionString 
 
 strSelect = SELECT * FROM& cstrFile 
 rs.Open strSelect，cn，adOpenForwardOnly，_ 
 adLockReadOnly，adCmdText 
 
设置db = CurrentDb 
设置rsDao = db.OpenRecordset（cstrDestination，_ 
 dbOpenTable，dbAppendOnly + dbFailOnError）
 
 Do While not rs.EOF 
 rsDao.AddNew 
对于每个fld In rs.Fields 
 strName = fld.Name 
 rsDao.Fields（strName）= rs.Fields（strName）.value 
下一页fld 
 rsDao.Update 
 rs.MoveNext 
 Loop 
 $ b b rsDao.Close 
设置rsDao =无
设置db =无
 rs.Close 
设置rs =无
 cn.Close 
设置cn =无
 End Sub 
  

这是我保存该过程的模块的声明部分： / p>

 选项比较数据库
选项显式
 #Const ProjectStatus =DEV'DEV或PROD 
  

更改这些常数的值以在系统上测试：

  Const cstrDestination As String =tblMaster
 Const cstrFile As String =temp.csv
 Const cstrFolder As String =C ：\share\Access
  

注意文件夹名称不包括尾部反斜杠。 p>

您将需要调整该过程以将文件名作为参数传递，而不是将其保留为常量。如果您的CSV文件存储在多个目录中，您也需要将文件夹名称作为参数传递。

希望显示您的操作方法对于一个文件将是足够的，然后你可以从这里处理所有的CSV文件。还请考虑添加错误处理。

So I navigated to the following MSDN Resource Page that addresses how to use ADO objects. My problem is that I cannot get it to work.

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.

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.

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.

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.

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.

解决方案

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.

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.

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.

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.

这篇关于在Access中使用ADO导入CSV数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！