Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Load Excel files into QlikView - Different Worksheet Names

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

5 Replies
MVP
MVP

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

Champion II
Champion II

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

MVP
MVP

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

table is @3

Champion II
Champion II

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

MVP
MVP