Can't read first row of CSV file

1.1k views Asked by At

I can't figure out why my script is not reading and storing the first row of the CSV file. It is starting with the second row for some reason.

I have the following code (reading from a 2 column CSV file):

Set rs = CreateObject("ADOR.Recordset")

'this just gets the folder where the csv file lives
sDir = GetiMacrosFolder("DataSources")

strConnect = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
             "DefaultDir=" & sDir & ";"

rs.Open "select * from test.csv", strConnect

count = 0

Do Until rs.EOR
    ReDim Preserve var1(count)
    var1(count) = rs.fields(0)

    ReDim Preserve var2(count)
    var2(count) = rs.fields(1)

    count = count + 1
    rs.MoveNext
Loop

rs.Close

If I then do a MsgBox(var1(1)), it shows me the value in row 3 and not row 2 like it should.

1

There are 1 answers

3
Ansgar Wiechers On BEST ANSWER

The Recordset object reads the first line of the CSV as the table headers, so the second line is the first data row. You can avoid this by using a driver that you can instruct not to do that:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDir & _
             ";Extended Properties=""text;HDR=No;FMT=Delimited"";"

or by placing a schema.ini like this alongside the CSV:

[test.csv]
Format=CSVDelimited
ColNameHeader=False
MaxScanRows=0
CharacterSet=ANSI

Note that CSVDelimited only works when your file is actually comma-separated and you have the comma defined as the field separator character in your system's regional settings. Otherwise you need to specify your delimiter character in that file:

[test.csv]
Format=Delimited(<delimiter>)
ColNameHeader=False
MaxScanRows=0
CharacterSet=ANSI