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.
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:or by placing a
schema.ini
like this alongside the CSV: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: