ADO Connection String to query a CSV File - "Not a Valid Path.." Error

Question

I can't seem to figure out what is wrong with my Connection String. I'm looking to query an Excel.CSV file.

Sub test()

Dim myConn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim myPath As String
Dim connStrng As String
Dim qryStrng As String
Dim ReiterName As String

myPath = "C:\Users\FirstName.LastName\Desktop\temp.csv"

connStrng = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & myPath & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""

Debug.Print connStrng

myConn.Open connStrng <-- 'C:\Users\FirstName.LastName\Desktop\temp.csv' Not a valid path error

Since it's a CSV file, do not include full path in the path, try only "C:\Users\FirstName.LastName\Desktop\" — Bharat
– Bharat, Commented Jul 26, 2021 at 8:38
Filename is the table specified in the SELECT, for example "SELECT * FROM temp.csv;" — Vincent G
– Vincent G, Commented Jul 26, 2021 at 9:24
BTW you should consider using ACE instead of JET (better support for 64 bits environments) — Vincent G
– Vincent G, Commented Jul 26, 2021 at 9:26

Bharat · Accepted Answer · 2021-07-26 09:36:23Z

1

Here is the full code for reference

Sub test()

    Dim myConn As New ADODB.Connection
    Dim recSet As New ADODB.Recordset
    Dim myPath As String
    Dim connStrng As String
    Dim qryStrng As String
    Dim ReiterName As String

    myPath = "C:\Users\FirstName.LastName\Desktop\"

    connStrng = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & myPath & ";" & _
              "Extended Properties=""text;HDR=YES;FMT=Delimited"""

    Debug.Print connStrng

    myConn.Open connStrng

    recSet .Open "SELECT * FROM temp.csv", myConn
    runSQLQueryForCSV = RecSet.GetRows()

    myConn.Close
    Set recSet = Nothing
    Set myConn = Nothing
End Function

edited Jul 26, 2021 at 9:36

answered Jul 26, 2021 at 9:31

Bharat

1,1958 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

ADO Connection String to query a CSV File - "Not a Valid Path.." Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related