ODBC removal of headers with preceding load

[chrismtb]

Hey everyone.

I am doing an ODBC load of xlsb data. Unfortunately the data tab  within the excel file has headers that are on the 4th row down.

I read a previous post on the forum about using a preceding load statement however when I try to replicate this no rows of data are loaded.

Below is an example of my script:

ODBC CONNECT32 TO [MyExcel;DBQ=PathAndFile.xlsb];

LOAD F1,
F2,
F3,
F4,
F5,
F6,
Row
where Row > '4';

LOAD F1,
F2,
F3,
F4,
F5,
F6,
rowno() as Row;

SQL SELECT *
FROM `PathAndFile.xlsb`.`'SheetA$'`;

what am I doing wrong?

Also in addition is there a way to pass these headers into the script without writing them by hand (note - I can't change the source data format) - I know you can specify with a "standard" Excel import such as .xlsx or .xlsm?

Thanks 

[chrismtb]

I have also tried:

where Row > 4

[Gysbert_Wassenaar · Accepted solution]

Why are you using ODBC to load data from excel instead of just loading directly from the excel file? That would give you the option to skip a number of header rows.

Anyway, Rowno() returns the row number of the record in the table you're creating. If you use it the way you do then the first records gets value 1 and then the next preceding load doesn't add it to the target table. So rowno  1 doesn't exists in the target table. Then the next record again gets rowno 1 and again is filtered out, etc, etc. So you get a result table with no records.

Try using the RecNo() function instead. That uses the record numbers of records read from the source table.

[chrismtb]

@Gysbert_Wassenaar  thanks for this which fixes my issue perfectly - in answer to your question my source is an XLSB file and I was under the impression I needed to use ODBC for the connection... is this not the case?

[Gysbert_Wassenaar]

You're probably right about xlsb files. I've never encountered them.