topic Re: ODBC removal of headers with preceding load in App Development

ODBC removal of headers with preceding load

chrismtb — Wed, 12 May 2021 15:05:59 GMT

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

Re: ODBC removal of headers with preceding load

chrismtb — Wed, 12 May 2021 15:07:01 GMT

I have also tried:

where Row > 4

Re: ODBC removal of headers with preceding load

Gysbert_Wassenaar — Wed, 12 May 2021 15:14:09 GMT

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.

Re: ODBC removal of headers with preceding load

chrismtb — Wed, 12 May 2021 15:45:59 GMT

@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?

Re: ODBC removal of headers with preceding load

Gysbert_Wassenaar — Wed, 12 May 2021 15:51:25 GMT

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