csv的ADODB连接字符串

Question

我想在Excel VBA中使用ADODB处理。csv文件。我尝试了一些在网上找到的字符串，但它们似乎都不起作用。我正在获取文件路径使用:

strVFile = Application.GetOpenFilename("CSV (*.csv), *.csv")

然后我将strVFile作为参数传递给子objReport.Load strVFile。子标题为:Public Sub Load(ByVal strFilename As String) .

然后我尝试使ADODB连接使用字符串:

pconConnection.ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & _
            ";Extended Properties=""text;HDR=Yes;FMT=Delimited(;)"";Persist Security Info=False"
    pconConnection.Open

当我运行宏并选择CSV文件时，出现"给定路径不是有效路径"的错误。我做错了什么?

编辑(代码),

Module mdlReport

Public Sub Report()
    Dim objReport As clsReport

    MsgBox "Please select .csv file", vbInformation + vbOKOnly
    strVFile = Application.GetOpenFilename("CSV (*.csv), *.csv")
    If strVFile <> False Then
        Set objReport = New clsReport
        objReport.Load strVFile
    End If
End Sub

类clsReport

Private pconConnection As ADODB.Connection
Private prstRecordset As ADODB.Recordset
Private Sub Class_Initialize()
  Set pconConnection = New ADODB.Connection
  pconConnection.ConnectionTimeout = 40
End Sub
Public Sub Load(ByVal strFilename As String)
    pconConnection.ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & _
            ";Extended Properties=""text;HDR=Yes;FMT=Delimited(;)"";Persist Security Info=False"
    pconConnection.Open
End Sub

Answer 1

对于文本文件，Data Source是文件夹，而不是文件。该文件是表(SELECT * FROM ..)。见http://www.connectionstrings.com/textfile

Answer 2

下面是使用Microsoft.ACE.OLEDB.16.0作为提供商的更新。

确保参考库"Microsoft ActiveX Data Objects 6.1 library"被添加到VBAproject中。

Sub testrunSQLQueryForCSV()
    Dim arrayTest
    arrayTest = runSQLQueryForCSV("C:xxxyyyy", "SELECT * FROM mycsvfile.csv")
    'NOTE: for CSV the Data Source reference is to the directory and the csv file is similar
    'to one "worksheet" in an excel file and is simply referenced in the SQL statement
End Sub

Public Function runSQLQueryForCSV(fileDirPath As String, SQLStatement As String)
    Dim Conn As New ADODB.Connection
    Dim RecSet As New ADODB.Recordset
    With Conn
       .Provider = "Microsoft.ACE.OLEDB.16.0"  'Can use many providers, but this is the latest and it works with csv files also
       .ConnectionString = "Data Source=" & fileDirPath & ";Extended Properties='text'"
    End With
    Conn.Open
    RecSet.Open SQLStatement, Conn
    runSQLQueryForCSV = RecSet.GetRows()
    Conn.Close
    Set RecSet = Nothing
    Set Conn = Nothing
End Function

Answer 3

我找到了问题的答案。对于文本文件(如remove所述)，Data Source只是文件夹路径，没有文件名。另外，不用:

C:dirdir2

我不得不使用

C:\dir\dir2\

从完整路径获取文件名:

strFilename = Dir(strFilepath)

只获取路径，不包含文件名:

strFilepath = Left$(strFilepath, InStrRev(strFilepath, ""))

要将路径格式从''更改为'\'，我只需使用:

strFilepath = Replace(strFilepath, "", "\")

问题解决了，谢谢关心。