Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Contributor III
Contributor III

ODBC removal of headers with preceding load

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 

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
chrismtb
Contributor III
Contributor III
Author

I have also tried:

where Row > 4

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

chrismtb
Contributor III
Contributor III
Author

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


talk is cheap, supply exceeds demand