How dynamically identify header size in xlsx files?

[vchuprina]

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?

[rwunderlich · Accepted solution]

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

[#3]

Thank you very much!!!