1 cell isn't read when using ADO to import data from .csv file

[JackDanIce]

Hi,

I have the following code which reads in data from a (closed) .csv file:

Sub ADO_Test()

    Dim ws              As Worksheet
    
    Dim objConnection   As Object
    Dim objRecordset    As Object
    
    Dim var             As Variant
    Dim start           As Variant
    
    Dim x               As Long
    Dim sql             As String
        
    Const fPath As String = "C:\Users\N582207\Desktop\"
    Const fName As String = "Test.csv"
        
    Const adOpenStatic As Long = 3
    Const adLockOptimistic As Long = 3
    Const adCmdText = &H1
       
    Set ws = Sheets("ADO_TEST"): ws.Cells.ClearContents
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    start = Timer
    
    'Create connection to source file
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fPath & ";" & "Extended Properties=""text;HDR=No;FMT=Delimited;"""
    
    'SQL query to set up a "table of data" to read into Excel
    sql = "Select * FROM " & fName
    objRecordset.Open sql, objConnection, adOpenStatic, adLockOptimistic, adCmdText

    'Read in header, can turn this into a IF statement since HDR in connection string is flag for header = yes/no
    With objRecordset
        For x = 0 To .Fields.count - 1
            If Right$(.Fields(x).Name, 7) <> ".NoName" Then ws.Cells(1, 1).Offset(, x).value = .Fields(x).Name
        Next x
    End With
  
    'Or Copy data into worksheet
    ws.Cells(i + 1, 1).CopyFromRecordset objRecordset
            
    Set ws = Nothing
    Set objConnection = Nothing
    Set objRecordset = Nothing
    
End Sub

This is based on code suggested here: http://www.mrexcel.com/forum/excel-questions/939434-reading-data-csv-files.html

Data in the csv file is four headers A1:D1 ("Header A, Header B, ..., Header D) with dummy data in A2:D5 ("A2","B2", ... "D2" for row 2 then "A3", "B3", ..., "D3" in row 3, with similar in rows 4 and 5). Running the code above brings in all the data, except that in A1 ("Header A") alone and I cannot figure out why.

Any suggestions?

TIA,
Jack

 

[RoryA]

The code itself is fine. Have you double-checked the saved csv?

 

[JackDanIce]

Hi Rory,

Yes (and verified yes again before replying back!), there's definitely a string in A1 of "Header A" but that alone doesn't read into destination sheet

Going to delete the destination sheet, add a new one, rename and try again and if that doesn't work, export the code to a new file and try. Will reply back with results

**As background, inherited a spreadsheet that opens/closes several .csv .xls and .xlsx files and copy/pastes data into destination sheet and with various filtering to cleanse the data.

Trying to speed this up and to learn something new, thought I'd start with reading the data from the .csv files without opening them (and if it works, extend to .xls and .xlsx files) and then use simple SQL queries to only import data of interest**

 

[JackDanIce]

Works in a newly added sheet - must have been something I didn't pick up affecting A1 of the destination sheet. Thanks for confirming the code is ok Rory.