topic Re: Load multiple excel files with different sheet names in QlikView

Load multiple excel files with different sheet names

Thu, 01 Aug 2013 11:09:58 GMT

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.

Re: Load multiple excel files with different sheet names

marcus_sommer — Thu, 01 Aug 2013 14:20:57 GMT

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

Re: Load multiple excel files with different sheet names

Thu, 01 Aug 2013 15:34:52 GMT

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)

thanks and regards.

Re: Load multiple excel files with different sheet names

Gabriel — Thu, 01 Aug 2013 15:49:35 GMT

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

Re: Load multiple excel files with different sheet names

Thu, 01 Aug 2013 15:53:06 GMT

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.

Re: Load multiple excel files with different sheet names

Gabriel — Thu, 01 Aug 2013 15:57:02 GMT

Hi,

I supose you can change thing in QlikView.

The script above is in QlikVIew.

Re: Load multiple excel files with different sheet names

rwunderlich — Thu, 01 Aug 2013 18:53:24 GMT

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

Re: Load multiple excel files with different sheet names

Fri, 02 Aug 2013 08:01:32 GMT

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

Thanks.