topic Re: Load from multiple Excel files just a specific sheet in QlikView

Load from multiple Excel files just a specific sheet

0li5a3a — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have other scenario rigarding loading from multiple excel fies just a specific sheet.

Every excel files got a name starting with 'abc 2' and a country name

Ex: abc2 from UK.XLSX

Every excel files contains 4 sheets and I want to load only the sheets no 3 which has this name 'TOP 60'and the contry name.

All The 'TOP 60* ' got the same coluns names.

I have attached the excel files format.

The problem which I have is that I need to load more then 100 files for every country.

Thanks all for help!

Regards,

Re: Load from multiple Excel files just a specific sheet

sunny_talwar — Fri, 19 Jan 2018 09:43:16 GMT

May be store the country name from the filename and then save that in the variable

Re: Load from multiple Excel files just a specific sheet

sunny_talwar — Fri, 19 Jan 2018 09:50:35 GMT

Something like this

For each File in FileList('abc*.xlsx')

TableName:

Load SubField(FileBaseName(), ' ', -1) as Country

From $(File)

(ooxml, no labels, table is [Sheet 1]);

LET vCountry = 'Top 60 ' & Peek('Country');

Fact:

LOAD *

From $(File)

(ooxml, no labels, table is [$(vCountry)]);

DROP Table TableName;

NEXT File;

Re: Load from multiple Excel files just a specific sheet

0li5a3a — Fri, 19 Jan 2018 10:15:16 GMT

Thanks, all good.

Re: Load from multiple Excel files just a specific sheet

0li5a3a — Fri, 19 Jan 2018 10:37:50 GMT

HI again,

If the names of the excel files are like below scenario?

Scenario:

Excel file name abc dsadsadsadasdas.xlsx

this contain 4 sheets and I want to load only sheet no 3 with this name: Top 60 saddsdnadnsabdasbnjknhd

Excel file name abc dsadsajdhsadhsadsajd.xlsx

this contain 4 sheets and I want to load only sheet no 3 with this name: Top 60 bbcscsbds

Excel file name abc dsadnasjdaskifh.xlsx

this contain 4 sheets and I want to load only sheet no 3 with this name: Top 60 asacsasadsadas

Excel file name abc sdadsnadnsadasdasjnhdasn.xlsx

this contain 4 sheets and I want to load only sheet no 3 with this name: Top 60 ssda

Here I can't apply your example. Coudl you please help me?

Regards,

Re: Load from multiple Excel files just a specific sheet

sunny_talwar — Fri, 19 Jan 2018 12:15:04 GMT

May be store the name of the sheet on the Sheet1

and then this script

For each File in FileList('abc*.xlsx')

TableName:

Load A as Country

From $(File)

(ooxml, no labels, table is [Sheet 1]);

LET vCountry = Peek('Country');

Fact:

LOAD *

From $(File)

(ooxml, no labels, table is [$(vCountry)]);

DROP Table TableName;

NEXT File;