Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load multiple excel files with different sheet names

Hello,

I want to load different excel files (one for each month), that have the same structure and similar name. I want to use the same load statement, i can do it with no problems using a wildcard for the excel name, but my problem is with the sheetname as it is different for each file.

Is there a way to use a wildcard in the sheet name?

Regards.

Tags (2)
1 Solution

Accepted Solutions
rwunderlich
Not applicable

Re: Load multiple excel files with different sheet names

I think you can just leave the sheet name off and it will default to the first sheet.

(ooxml, embedded labels);

-Rob

http://masterssummit.com

http://robwunderlich.com

7 Replies
marcus_sommer
Not applicable

Re: Load multiple excel files with different sheet names

If it is always the same sheet-position you could use:

(biff, no labels, header is 1 lines, table is @4)

If not you could use a for loop and creates for every loop a variable:

(biff, no labels, header is 1 lines, table is '$(vSheet)')

- Marcus

Not applicable

Re: Load multiple excel files with different sheet names

Hi,

It is always the same position for the sheet (the first one as there is only one sheet) and for every column, the only change is the name of the Sheet.

The first option seems to be the solution, I tried but i get an error (Cannot read BIFF file).

The excel structure is as this: (its an xlsx)

excel.JPG

thanks and regards.

Gabriel
Not applicable

Re: Load multiple excel files with different sheet names

Hi,

Can I suggest you replace the date part or whatever differentiat your file everymonth with asteric (*). as in

FROM

[.....\New_Professions_full_data_set_01/08/2013.xlsx]

(ooxml, embedded labels, table is Health);

with

FROM

[.....\New_Professions_full_data_set_*.xlsx]

(ooxml, embedded labels, table is Health);

Let me know if this helps

Best Regards,

Gabriel

Not applicable

Re: Load multiple excel files with different sheet names

Hi,

The problem is that I can not change anything in the Excel files, they are created automatically by another process not related to me.

Gabriel
Not applicable

Re: Load multiple excel files with different sheet names

Hi,

I supose you can change thing in QlikView.

The script above is in QlikVIew.

rwunderlich
Not applicable

Re: Load multiple excel files with different sheet names

I think you can just leave the sheet name off and it will default to the first sheet.

(ooxml, embedded labels);

-Rob

http://masterssummit.com

http://robwunderlich.com

Not applicable

Re: Load multiple excel files with different sheet names

That's it, it is always the simpler solutions that work.

Thanks.