Load Excel files into QlikView - Different Worksheet Names

[Anonymous]

Hi all ..

here is the problem I am facing:

I have to load multiple excel files from the same folder. They are named 02_January, 02_February, ... etc, so I am using the ' * ' function in my script.

In the excel file however, the data that I need is always on the third worksheet and the worksheets are named like this: January, February, March...

I know how to loop through one file with different worksheet names but I cannot solve this problem.

Any ideas?

Best,

Nadine

[swuehl]

Instead of the Wildcard '*', you can use a FOR EACH ... NEXT loop:

For each..next ‒ QlikView

You can also parse the file name in your variable and use it to address your sheets, if that's your issue (or maybe I haven't fully understood what your issue is).

[vinieme12]

if you know you always have to load the the third worksheet them simply peek() the name

As peek start from 0, 3rd sheet is always row 2 in peek()

Comment the for loop

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\vp51284\Downloads\Data 2014.xlsx];

LET vfile = 'C:\Users\vp51284\Downloads\Data 2014.xlsx';

exceltables:

SQLTABLES;

DISCONNECT;

//For i = 0 To NoOfRows('exceltables')-1

Let zSheet = purgechar(purgechar(Peek('TABLE_NAME', 2, 'exceltables'),chr(36)),chr(39)); //Remove $ and ' from sheetname string

FACT:

LOAD *,

'$(zSheet)' as FromSheet

FROM

  (ooxml, embedded labels, header is 0 lines, table is [$(zSheet)]);

//Next

[swuehl]

Not  sure if this is your issue, but I think you can address the excel sheet also just using

table is @3

[vinieme12]

yes, but it works  only with .XLS and not .XLSX

[Kushal_Chawda]

see this

Dynamically Loading Multiple Excel Files