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

derik81

New Member
Joined
Jul 17, 2021
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

derik81

New Member
Joined
Jul 17, 2021
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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

1626522539875.png


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

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
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) …​
 

Forum statistics

Threads
1,147,632
Messages
5,742,232
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top