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

hindersalivaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hindersalivaAuthor Commented:
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.
0
Fabrice LambertFabrice LambertCommented:
You might need to write a Schema.ini file, describing the CSV file.

There a great (and humorous) article about this on MSDN:
https://msdn.microsoft.com/en-us/library/ms974559.aspx
1
NorieAnalyst Assistant Commented:
Can you post a sample of the CSV with dummy data?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

hindersalivaAuthor Commented:
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
0
hindersalivaAuthor Commented:
Norie, see my comment and attached file 'Friends.csv'
0
NorieAnalyst Assistant Commented:
Where's your code to get the headers?

I would expect to see something like this which puts the headers in row 1 and then the data starting in row 2.
Dim a As ADODB.Recordset
Dim fld As ADODB.Field
Dim rng As Range

    Set a = getData("Friends.csv")
    
    a.Open

    Set rng = Range("A1")
    
    For Each fld In a.Fields
        rng.Value = fld.Name
        Set rng = rng.Offset(, 1)
    Next fld

    Range("A2").CopyFromRecordset a

    a.Close

Open in new window


By the way, I know you close the recordset but you really should close the connection too.
0
hindersalivaAuthor Commented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

perhaps you might reference the CSV file directly as a table in an SQL statement.
SELECT A.* FROM
[Text;FMT=Delimited;HDR=YES;CharacterSet=437;DATABASE=c:\path].[Filename.csv]  as A

Open in new window

You may need to change some of the parameters like CharacterSet and HDR (header row)

fyi, CopyFromRecordset works with both ADO and DAO recordsets

*  I realize this is an Excel question -- you can test this directly in Access by setting SQL of a query to see if you get records; if you can link, then you can construct SQL for a recordset in code too

~~~
as Norie mentioned, your code does not write the headers...

here is another method, written by NateO, for writing headers:
  'Field Names - Stack into Array
  Dim fldArr() As String

   'more code

    With rs
      'Stack a String Array with the Field Names
      ReDim fldArr(0 To .Fields.Count - 1)
 
      For j = LBound(fldArr) To UBound(fldArr)
        Let fldArr(j) = .Fields(j).Name
      Next j

   'more code

           'Pass our dynamic Field String Array to A1,
           'stretched to the Right for number of Elements
            ws.Range("a1").Resize(, UBound(fldArr) + 1).Value = fldArr

Open in new window

If you are getting some headers but not others, I might question the format of the file ... perhaps better to open it and use some logic to fix what is wrong then save (or save copy), close, and link. You could loop too but with large files, it might be faster to link. There might also be a difference in being able to correctly parse (good article, Fabrice -- and it was funny too).

~~~
if the goal is to get the data into Excel, you can also do something like this:
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;c:\path\filename.csv", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "MyTablename"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Open in new window


have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hindersalivaAuthor Commented:
Sorry it took so long to close this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ADO

From novice to tech pro — start learning today.