topic How can I dynamically reference the first sheet in an Excel Document in QlikView

How can I dynamically reference the first sheet in an Excel Document

wms_manis — Wed, 01 Jul 2015 21:45:44 GMT

I'm pretty sure I have done this before but I don't recall how it was done.

I am loading in a bunch of spreadsheets using the *.

Load *

FROM

[SOTL Week*.xlsx]

(ooxml, embedded labels, header is 1 lines, table is LYNX);

Unfortunately some of the SOTL Week ##.xlsx files don't have the sheet name LYNX. So to overcome this, if I could simply reference the first sheet in the workbook, without regard to how it was named, my files would load.

Thanks!

Wade

Re: How can I dynamically reference the first sheet in an Excel Document

maxgro — Wed, 01 Jul 2015 21:53:28 GMT

load all sheets of all files SOTL Week*

in bold the sheets loop

DIRECTORY;

Table:

load '' as field autogenerate 0;

For Each vFile in FileList('SOTL Week*.xlsx')

trace file=$(vFile);

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

trace $(vSheet);

concatenate(Table)

LOAD '$(vSheet)' as [Tab Name],

From [$(vFile)]

(ooxml, embedded labels, table is [$(vSheet)]);

Next;

NEXT;

Re: How can I dynamically reference the first sheet in an Excel Document

wms_manis — Thu, 02 Jul 2015 13:41:07 GMT

I like it. Worked well. Thanks!

Sent from my iPhone