Help for labelling each data with its sheet name

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!

Input

Help

Accepted answers

anjaliaithal

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

All comments

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

sheetname.yxmd

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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335