topic Re: How dynamically identify header size in xlsx files? in QlikView

How dynamically identify header size in xlsx files?

vchuprina — Mon, 26 Jan 2015 14:09:35 GMT

I received files with different header size. Sometimes data start from 10 row, sometimes from 13.

In picture you can see part of my file.

Part of my script:

FROM

[\\449629-file1\Data - new\Current\MIS-IB004*.xlsx]

(ooxml, embedded labels, header is 6 lines);

Header size in wizard now 6.

So how dynamically identify header size? What should I change in my script?

Re: How dynamically identify header size in xlsx files?

rwunderlich — Mon, 26 Jan 2015 18:14:46 GMT

The trick is to identify on what row you can find a recognizable column heading and then use that information to set a "Header is n lines" variable. Based on your picture, I'll assume we are looking for "StoreDC" in column A as the column header.

FindHeaderTemp:

LOAD RecNo() as DataStart

FROM [foo]

(biff, no labels, table is Sheet1$)

WHERE @1 = 'StoreDC'

;

LET vHeaderSize = peek('DataStart') - 1;

DROP TABLE FindHeaderTemp;

and now use vHeaderSize in your actual load:

LOAD *

FROM [foo]

(biff, embedded labels, header is $(vHeaderSize) lines, table is Sheet1$);

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How dynamically identify header size in xlsx files?

Fri, 10 Jun 2016 10:19:14 GMT

Thank you very much!!!