Load from multiple Excel files just a specific sheet

[0li5a3a]

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,

C

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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;

[0li5a3a]

Thanks, all good.

[0li5a3a]

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,

C

[sunny_talwar]

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;