0

I want to query a CSV file saved as an Excel file using SQL. I'm unable to even connect to or "open" the file. It throws up the Run-time error '-2147467259 "P:\myDocs\FileName.csv is not a valid path" error.

My Connection string

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Firstname.Lastname\Downloads\temp.csv;Extended Properties="text;HDR=YES;FMT=Delimited;"

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 = "P:\myDocs\FileName.csv"

'Connection string
connStrng = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myPath _
& ";Extended Properties=""text;HDR=YES;FMT=Delimited;"""

myConn.Open connStrng <--Error
OldNick
  • 43
  • 6
  • 1
    Stupid question. Is `P:\myDocs\FileName.csv` a valid path? – John Wu Jul 21 '21 at 15:15
  • @JohnWu It is, it was the first thing i checked. – OldNick Jul 21 '21 at 15:18
  • 1
    How did you check? I suggest, for troubleshooting purposes, that you add a bit of code to your VBA to [check programmatically](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists/33771924). The context might be different, i.e. the drive letter might not work within your VBA process. – John Wu Jul 21 '21 at 15:22
  • @JohnWu I tried opening the file using workbooks.open, it worked. – OldNick Jul 21 '21 at 15:29
  • 1
    Are there any special characters in the path or filename? It's possible this connection string can't handle certain characters in the path. (Rename the CSV file to be only letters and numbers and open that, like rename it to "temp.csv" and then delete the tempfile when done) – HackSlash Jul 21 '21 at 16:12
  • @HackSlash, I renamed and saved the file on my desktop, I have edited the question to show wht my connection string looks like. – OldNick Jul 22 '21 at 09:31
  • If that's your actual connection string I would recommend adding quotes around the path to see if that helps. – HackSlash Jul 22 '21 at 15:42

1 Answers1

1

I guess the op almost certainly answered their own question but I thought I'd post the answer here in case anyone comes across this post looking for an answer as I did.

In case the credit link below disappears, I resolved this issue by not including the name of the file itself in the path, i.e. the op's path should read P:\myDocs\\ (the file name to be later used in a SQL statement string used in the .Open method).

Unfortunately it seems the ACE.OLEDB.12.0 provider rather counter-intuitively doesn't work in the same way for a text file path as it does for an access file path which does include the access DB file name. I guess the rationale is that a path can contain several files (for use with .Open) in the same way as an access DB can contain several tables (for use with .Open - although obviously .Open is VBA's implementation).

Anyway, I found the answer here.

Toni
  • 1,555
  • 4
  • 15
  • 23
vonevilstein
  • 26
  • 1