vba .csv 的 ADODB 连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7315957/
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
ADODB Connection String for .csv
提问by Jandrejc
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:
我想在 Excel VBA 中使用 ADODB 处理 .csv 文件。我尝试了一些在网上找到的字符串，但它们似乎都不起作用。我正在使用以下方法获取文件路径：
strVFile = Application.GetOpenFilename("CSV (*.csv), *.csv")
And then I pass strVFileas a parameter to the sub objReport.Load strVFile. The header of the sub is: Public Sub Load(ByVal strFilename As String).
然后我strVFile作为参数传递给 sub objReport.Load strVFile。子的标题是：Public Sub Load(ByVal strFilename As String)。
Then I try to make ADODB connection using 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
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?
当我运行宏并选择 CSV 文件时，出现错误，提示“给定路径不是有效路径”。我究竟做错了什么？
Edit (Code),
编辑（代码），
Module mdlReport
模块 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
Class clsReport
类 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
采纳答案by Jandrejc
I found the answer to my problem. For text files (as stated by Remou) Data Sourceis just the folder path, without file name. In addition instead of using:
我找到了我的问题的答案。对于文本文件（如 Remou 所述）Data Source只是文件夹路径，没有文件名。此外，而不是使用：
C:\dir\dir2\
I had to use
我不得不使用
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.
问题已解决，感谢关注。
回答by Fionnuala
For a text file, Data Sourceis the folder, not the file. The file is the table (SELECT * FROM ..). See http://www.connectionstrings.com/textfile
对于文本文件，Data Source是文件夹，而不是文件。该文件是表 (SELECT * FROM ..)。请参阅http://www.connectionstrings.com/textfile
回答by TEK
Here is an update using Microsoft.ACE.OLEDB.16.0 as provider.
这是使用 Microsoft.ACE.OLEDB.16.0 作为提供程序的更新。
Make sure the reference library "Microsoft ActiveX Data Objects 6.1 Library" is added to VBAproject first.
确保首先将参考库“Microsoft ActiveX Data Objects 6.1 Library”添加到 VBAproject。
Sub testrunSQLQueryForCSV()
Dim arrayTest
arrayTest = runSQLQueryForCSV("C:\xxx\yyyy\", "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
