Avatar of hindersaliva
hindersaliva
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel VBA - Get data from a closed CSV. Headers?

I'm experimenting with code I have found here
https://stackoverflow.com/questions/11635526/return-csv-file-as-recordset

It is supposed to get data from a CSV file, without opening it, and paste the data at "A1" on the activesheet. However, even though the connection specified that it has Headers (HDR=Yes) only some headers are coming through for me. The first row is row 1 of the data in the CSV.

Do I need to get the Headers separately? If so, how? (SEE UPDATE BELOW)
Thanks for any pointers.

Public Function getData(fileName As String) As ADODB.Recordset

    Dim path As String
    path = "C:\ExcelOMG Cookbook 2016\Closed CSV\"
    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset
'    cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
'                   "Data Source=" & path & ";" & _
'                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
                   
                   
    cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
                   
                   
                   
    RS.ActiveConnection = cN
    RS.Source = "select * from " & fileName
    Set getData = RS

End Function


Sub GetDataFromCSV()
    Dim a As ADODB.Recordset
    Set a = getData("DataFile.csv")
    a.Open
    
    Range("A1").CopyFromRecordset a
    
    a.Close

End Sub

Open in new window

* ADOVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
hindersaliva

8/22/2022 - Mon
hindersaliva

ASKER
UPDATE:
I changed the Provider to Provider=Microsoft.ACE.OLEDB.12.0
Now I get a row for the Headers but only some headers come through.

I'm going to dig into this a little more. See if the CSV file is odd.
SOLUTION
Fabrice Lambert

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Norie

Can you post a sample of the CSV with dummy data?
hindersaliva

ASKER
I tried it with the attached file Friends.csv. Curiously when I put 'Headers=Yes' I get no headers. When I put 'Headers=No' I do get headers.

Same result with JET and ACE.

Can someone explain what's happening here please?
Friends.csv
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
hindersaliva

ASKER
Norie, see my comment and attached file 'Friends.csv'
SOLUTION
Norie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hindersaliva

ASKER
Norie, I can see I would need a separate 'GetHeaders' procedure. But I DO get some of the Headers, but not all. That's the mystery.
ASKER CERTIFIED SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
hindersaliva

ASKER
Sorry it took so long to close this question.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.