CODEWITHSUNDEEP
csvvbscriptimacros
Jack Hillard
Jack Hillard

Reputation: 596

Can't read first row of CSV file

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.

Upvotes: 1

Views: 1179

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

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

Upvotes: 3

Related Questions