Load Wildcard on "Table is"

[bnelson111]

Help needed on what's probably a simple operator but I cant get it. I have a basic excel load statement ending with the following.

S:\Supervisor\qlikview\Operators.xls

(biff, embedded labels, table is [Sheet1$]);

If i have other worksheets in the same excel work book Operators.xls, how can i load all.

i thought it would be like:

S:\Supervisor\qlikview\Operators.xls

(biff, embedded labels, table is ['*']);

but it does not work.

[#2 · Accepted solution]

Hi,

* will not work for sheet names. If you know the total no. of sheets and if all the sheets are with default names then you can use below script

FOR I = 1 to $(vNoOfSheets)

     LOAD a, b, c

     From MyExcel.xls

     (biff, embedded labels, table is [Sheet$(I)]);

NEXT I;

If your excel file is having customzied sheet names then use like below

SET vSheet1 = 'FirstSheet';

SET vSheet2 = 'SecondSheet';

SET vSheet3 = 'ThridSheet';

FOR I = 1 to 3

LOAD a, b, c 

     From MyExcel.xls

     (biff, embedded labels, table is [$(Sheet$(I))]);

NEXT I;

[#3]

try this

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 4;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

[tresesco]

One way is to create ODBC connection, and trying something like as described here

Or,

If you have limited number of sheets then maintain a list of those sheet names in an INLINE load and try something like:

Test:
LOAD * INLINE [
    Sheetname
    Sheet1$
    Sheet2$
    Sheet3$
];

For i = 0 to 2

Let vTest =  peek('Sheetname','$(i)','Test');

LOAD Date,
     '$(vTest)' as tt,
     Value
FROM
..\test.xls
(biff, embedded labels, table is '$(vTest)');

next i