How to make a sql connection to CSV file from Excel VBA?

[derik81]

I need to connect to CSV file in my downloads folder using SQL query from Excel VBA. This is the code I found from internet

Sub SQLconnectCSV()
    Dim xlcon As ADODB.Connection
    Dim xlrs As ADODB.Recordset
    
    Set xlcon = New ADODB.Connection
    Set xlrs = New ADODB.Recordset
    
    csvfile = Environ$("USERPROFILE") & "\Downloads\data.csv"
    
    xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"
    xlcon.ConnectionString = "Data Source=" & csvfile & ";" & _
            "Extended Properties=""text;HDR=Yes;FMT=Delimited;"""
    
    xlcon.Open
    
    SQLQuery = "SELECT * FROM [data.csv]"

    xlrs.Open SQLQuery, xlcon
    
    Worksheets("MortgageDefaultData").Cells(1, 1).CopyFromRecordset xlrs
    
    xlrs.Close
    xlcon.Close
    
    Set xlrs = Nothing
    Set xlcon = Nothing
End Sub

I have never worked with SQL in excel and I am confused about this code

xlcon.Provider = "Microsoft.Jet.OLEDB.4.0"

, I get an error saying provider not found it may not be installed correctly. So I wanted to know How can I fix that and also should I mention that to connect to CSV file with SQL queries with VBA?

 

[derik81]

I have fixed that issue by changing to "Microsoft.ACE.OLEDB.12.0" and after that when I do xlxon.open it throws me an error

The file is existing and I tried with different files as well but It says file doesn't exisit.

 

[Marc L]

​

As you import all the csv text file so why do you not use directly any Excel import feature ?!​

​

Revert what returns the codeline MsgBox Dir(csvfile) …​