Load multiple excel with multiple sheets having came columns with file and sheet name

[Anonymous]

Hi,

Let me please explain the situation that I have.

There are different files for each month as shown below

Raw Data 2018 01.xls

Raw Data 2018 02.xls

Raw Data 2018 03.xls

Raw Data 2018 04.xls

Now each file have following sheets

A11

A12

A13

A14

A15

And each Sheet has following data

Date

Product

Value

I want to Load data in one single table with following fields (and only from Sheet A12 & A13) from each file.

Date

Product

Value

[Sheet Name]

To load data from multiple files I can use "*" in the file name (Raw Data *.xls), but dont know how to just load data from from only two sheet and to add sheet name as field value for new column.

Thanks in advance for your help!!

[andrey_krylov]

Hi, Samik. Try below code

FOR Each Sheet in 'A12', 'A13'

  [Table]:

  LOAD

     Date,

     Product,

     Value,

     '$(Sheet)' as [Sheet Name],

  FROM [YourPath\Raw Data *.xls] (ooxml, embedded labels, table is $(Sheet));

NEXT