Dynamic header size in a load script

[Kotty]

Hello,

I'm having a trouble to set a header size of an XLSX file dynamically in a load script. For example I want to set the 'header line is' part of the file specification to: 

header line is $(vHeaderSize) lines

where a variable vHeaderSize  is always an integer. However QS doesn't seem to accept anything but a whole number in that spot. However some older topics on this forums suggest that it should be possible:

• https://community.qlik.com/t5/QlikView-App-Development/Dynamic-header-size-in-script-load/td-p/55636...
• https://community.qlik.com/t5/QlikView-Scripting/How-dynamically-identify-header-size-in-xlsx-files/...

At least in QlikView anyway. Is QS any different in this regard? Or has it been changed with some later update?

Would anyone be able to help me with this?

Thank you.

[Kotty]

Bump

[Kotty]

Does nobody know the answer? 😞

[brunobertels]

Hi 

Got the same issue today , it seem's like qlik sense does'nt accept this syntax, and only wait for an integer as number of line header. 

Unless you have find another solution since april 2019 ? 

Regards

[ArnadoSandoval · Accepted solution]

Hi @Kotty  and @brunobertels 

You are talking about having a dynamic Format Specification; you can do that by assigning the whole format specification to a variable, actually to a SET variable that behaves like a function; below the Format Specification Set variable definition:

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

Notice that the whole Load Specification was assigned to a Set variable named: vLoadSpec; with the variable vHeaderSize becoming the placeholder for the Header Size; then we could use it like this:

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

 When we set the variable vHeaderSize to a value, followed by assigning our Set variable (vLoadSpec) to the variable rtLoadSpec (run time load spec), the next time we need to change the header location we could write:

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

Finally, our Load statement reference the rtLoadSpec variable like shown below:

Set vLoadSpec = '(ooxml, embedded labels, header is ' &(vHeaderSize) & ' lines, table is [Sample Data])';

Set vHeaderSize = 5;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 
Header_at_5:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-01.xlsx]
$(rtLoadSpec);

Let vHeaderSize = 6;
Let rtLoadSpec = $(vLoadSpec);

NoConcatenate 

Header_at_6:
LOAD
    "Month",
    "Date",
    A,
    "B",
    C,
    A1,
    B1,
    C1
FROM [lib://Excel_Data/Excel Load-02.xlsx]
$(rtLoadSpec);

Hope this helps,

[Kotty]

It works like a charm! You've been very helpful, thank you!

[ArnadoSandoval]

@Kotty 

Cool, I answered a one year, one month and 19 days old question 😎

[Kotty]

Time flies 😀 

Before you've provided me your awesome solution I simply used the "if" function that would run appropriate load script with hardcoded header size based on the header size that'd been passed from the call script as an argument. 

There've been only 3 possibilities for the row position of the header so far so the original script wasn't terribly long but your solution is a lot more elegant.

[brunobertels]

Hi All 

Thanks @ArnadoSandoval for your solution. I have 50 % of my requierment solved with your response. 

I Need to implement this now in a script where i load each month an excel file with multiple sheet ( between 4 and 6 but it can changed from one month to another and for some Reason the header is floating between line 8 and 9 due to parameter info in the first lines of the sheet. 

So  I have an ODBC connection and a for loop to check and load the different sheet and need now to implement your script. 

the header  line number is checking with a fieldindex( field ,'value') statement 

Thank again 

Bruno

[ArnadoSandoval]

Hi @brunobertels 

I suggest you to create a new topic for your next question, also would you elaborate more when you wrote

the header  line number is checking with a fieldindex( field ,'value') statement 

I was unable to translate that to code in my mind!

Regards,