Help for labelling each data with its sheet name

[Eug_teng]

I have an excel file with multiple sheets (Same format) and would like to label each record with the originating sheet name / number when I import all of them together. How do I go about doing this?

E.g Sheet 1

Code	Number
a	123
b	234
c	345

E.g Sheet 2

Code	Number
e	456
f	567
g	678

E.g Output

Code	Number	Sheetname
a	123	1
b	234	1
c	345	1
e	456	2
f	567	2
g	678	2

Thank you!

[anjaliaithal]

1) Make sure you have chosen 'Full Path' in Output File Name as field

Your output will have a column with full Path like 'Folder\Filename.xlsx||||`Sheet1$`

2) After Union of two sheets, add a Text to columns with the following config:

One of your output columns is like this

3) At the end, add a data cleansing tool

[PhilippK]

Hi @Eug_teng ,

you can use a FORMULA tool to add a new "Sheetname" column (see attached).

Or you do it dynamically, e.g. by importing the list of sheet names first:

 

Best 

Phil

[Eug_teng]

Hi Phil,

Thanks for the suggestion!

But what if all the sheets are in 1 excel file and I want to use only 1 input tool?

[Eug_teng]

Hi anjaliaithal,

Thanks for the suggestion!

But what if all the sheets are in 1 excel file and I want to use only 1 input tool?

[anjaliaithal · Accepted solution]

First Part of Workflow

1) Input Tool Config

Output

2) Formula For new File Name

Output

3) Dynamic Input Tool Config

      a)In the Input DataSource Template, click 'Edit'. Choose Sheet1 (You can choose Sheet 2 also)

             Make sure you choose 'File Path' in the highlighted section. Click on OK

      b) Complete the config as below

Output

Second Part: Please lookup to my first answer of  using text to columns and DataCleansing

[Eug_teng]

Thank you! This works:)