ADODB Connection String for .csv

Question

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")

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).

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

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?

Edit (Code),

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

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

Temporarily use Debug.Print or MsgBox to show the value of strFilename immediately before you use it in the connection string. Check that it shows a full path to the required file. Also, Delimited(;) should be Delimited(,) for a CSV I would think — barrowc
– barrowc, Commented Sep 6, 2011 at 7:00
I've added some code, it's just a few lines, but it should show my problem. I've tried to look at the path and it seems to be OK. — Jandrejc
– Jandrejc, Commented Sep 6, 2011 at 7:02

Fionnuala · Accepted Answer · 2011-09-06 08:09:21Z

13

For a text file, Data Source is the folder, not the file. The file is the table (SELECT * FROM ..). See http://www.connectionstrings.com/textfile

answered Sep 6, 2011 at 8:09

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TEK · Accepted Answer · 2018-10-13 02:02:18Z

Here is an update using Microsoft.ACE.OLEDB.16.0 as provider.

Make sure the reference library "Microsoft ActiveX Data Objects 6.1 Library" is added to VBAproject first.

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

Jandrejc · Accepted Answer · 2011-09-06 08:17:03Z

1

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\

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.

answered Sep 6, 2011 at 8:17

Jandrejc

4993 gold badges9 silver badges19 bronze badges

Collectives™ on Stack Overflow

ADODB Connection String for .csv

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related