Sporadic Headers When Reading a CSV into a Recordset

[CarlRostron]

I have no clue as to why when I read a CSV file into a recordset, the headers are not all being read. I get gaps in the headers for whatever reason. Attached below is the Function to load the recordset from the file. I have hard coded Hdr to "No" in the connection string.

Function QueryByID(tableName As String, fieldToQuery As String, Target As Long, Hdr As String) As Recordset
    Dim strFilePath, strFileName, strQuery As String
    Dim oFSObj, oConn As Object
    Dim oRs As Recordset
    Dim f, lastRow As Integer
 
    theUser = GetUserName
    theFileName = "C:\Documents and Settings\" & theUser & "\My Documents\Reporting\" & tableName
    Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
    strFilePath = oFSObj.GetFile(theFileName).ParentFolder.Path
    strFileName = oFSObj.GetFile(theFileName).Name
   'Open an ADO connection to the folder specified
    Set oConn = CreateObject("ADODB.CONNECTION")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strFilePath & ";" & _
               "Extended Properties=""text;HDR=No;FMT=Delimited(,)"";"
    Set oRs = CreateObject("ADODB.RECORDSET")
    'Now actually open the text file and import into Excel
   If (Target = -1) Then
     strQuery = "SELECT * FROM [" & strFileName & "];"
   Else
     'Now actually open the text file and import into Excel
     strQuery = "SELECT * FROM [" & strFileName & "] WHERE [" & fieldToQuery & "] = " & Target
   End If
   'strQuery = "SELECT * FROM [" & strFileName & "]"
   oRs.Open strQuery, oConn
   'MsgBox oConn
 
  'PrintQueryResults
  Set QueryByID = oRs
 
End Function

The below is how I output the resultset:

Sub OutputResultSet(aTable As String)
  Dim row, col, i, j As Integer
  row = 1
  col = 1
  ThisWorkbook.Sheets(aTable).Activate
  Sheets(aTable).Cells.ClearContents
  rs.MoveFirst
 
  Do While Not rs.EOF
    For j = 1 To rs.Fields.Count
      Cells(row, col).Value = rs.Fields(j - 1)
      col = col + 1
    Next j
    rs.MoveNext
    row = row + 1
    col = 1
  Loop
End Sub

This has stumped me for a few hours. I have triple checked the CSV file and the first row in each file has definitely got headers. Some headers are loaded and output fine, others are just blank.

Please help.

 

[Colin Legg]

Hi,

Use the rs.Fields().Name to get the field names - this will be a simple loop through each field in the collection.

Then you can use rs.Fields().Value to get the data using a loop as you currently are, or alternatively the Range class has a CopyFromRecordSet method which can be used to swiftly put the data from a recordset onto a sheet without the need to loop.

 

[CarlRostron]

Thanks for a speedy reply. I will try this tomorrow and repost if I still have issues. I just thought that if I had set headers to 'no' in the connection string it would have loaded all the table together and I would have still been able to reference the headers parsing it myself. If I do as you say, will i leave the header as 'no' still or would this be now set to 'yes'.

Thanks

 

[Colin Legg]

I didn't check your connection string when I read your post.

The header should be set to yes because your CSV file contains column headers. If it's set to no then the field names would be considered as data.

 

[CarlRostron]

ok, i will try it. Thanks again.

 

[CarlRostron]

Colin - this works like a dream. I appreciate your help.