Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

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?

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Thank you very much!!!