topic Re: Load Excel files into QlikView - Different Worksheet Names in QlikView

Load Excel files into QlikView - Different Worksheet Names

Anonymous — Tue, 20 Jun 2017 09:00:48 GMT

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

Re: Load Excel files into QlikView - Different Worksheet Names

swuehl — Tue, 20 Jun 2017 10:01:10 GMT

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).

Re: Load Excel files into QlikView - Different Worksheet Names

vinieme12 — Tue, 20 Jun 2017 10:03:02 GMT

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

Re: Load Excel files into QlikView - Different Worksheet Names

swuehl — Tue, 20 Jun 2017 10:08:59 GMT

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

table is @3

Re: Load Excel files into QlikView - Different Worksheet Names

vinieme12 — Tue, 20 Jun 2017 10:14:08 GMT

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

Re: Load Excel files into QlikView - Different Worksheet Names

Kushal_Chawda — Tue, 20 Jun 2017 10:23:29 GMT

see this

Dynamically Loading Multiple Excel Files