Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

How dynamically identify header size in xlsx files?

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

Header.png

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?

1 Solution

Accepted Solutions

Re: How dynamically identify header size in xlsx files?

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

2 Replies

Re: How dynamically identify header size in xlsx files?

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

Not applicable

Re: How dynamically identify header size in xlsx files?

Thank you very much!!!

Community Browser