.csv的ADODB连接字符串 [英] ADODB Connection String for .csv

问题描述

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

I want to process .csv files with ADODB in Excel VBA. I tried a few strings found on web, but none of them seems to work. I'm getting file path using:

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

然后我将 strVFile 作为参数到子 objReport.Load strVFile 。子标题是： Public Sub Load（ByVal strFilename As String）。

And then I pass strVFile as a parameter to the sub objReport.Load strVFile. The header of the sub is: Public Sub Load(ByVal strFilename As String).

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

Then I try to make ADODB connection using 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

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

When I run the macro and choose CSV file, there's error occuring saying that "given path is not a valid path". What am I doing wrong?

编辑（代码），

模块mdlReport

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

Class 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

推荐答案

我找到了我的问题的答案。对于文本文件（如Remou所述）数据源只是文件夹路径，没有文件名。另外不使用：

I found the answer to my problem. For text files (as stated by Remou) Data Source is just the folder path, without file name. In addition instead of using:

C:\dir\dir2\

我不得不使用

C:\\dir\\dir2\\

从完整路径获取文件名：

To get file name from full path:

strFilename = Dir(strFilepath)

仅获取路径，无文件名：

To get the path only, without a file name:

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

要将路径格式从\更改为'\\'我刚刚使用：

To change path format from '\' to '\\' I just used:

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

由于兴趣，问题解决了。

The problem is solved, thanks for interest.

这篇关于.csv的ADODB连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！