I am a bit stuck with a pretty simple problem. I have multiple sheets in an Excel file and want to import all using the input + dynamic input tool, but want to add the sheet name to every data row. Example: Sheet: Books: KeyValue1A2B3C4D Sheet Videos: KeyValue5E6F7G8H Goal: KeyValueCategory1ABooks2BBooks3CBooks4DBooks5EVideos6FVideos7GVideos8HVideos What's the best/easiest way to do it? You'll find a sample file attached. minimal_sample.xlsx

Import multiple Excel Sheets with sheet name as column

I am a bit stuck with a pretty simple problem.

I have multiple sheets in an Excel file and want to import all using the input + dynamic input tool, but want to add the sheet name to every data row.

Example:

Sheet: Books:

Key	Value
1	A
2	B
3	C
4	D

Sheet Videos:

Key	Value
5	E
6	F
7	G
8	H

Goal:

Key	Value	Category
1	A	Books
2	B	Books
3	C	Books
4	D	Books
5	E	Videos
6	F	Videos
7	G	Videos
8	H	Videos

What's the best/easiest way to do it?

You'll find a sample file attached.

minimal_sample.xlsx

Input

Accepted answers

afv2688

Hello @grossal,

This macro reads all the sheets or the ones you specifies and unions them all

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Read_excel.zip

1737280

Here's a simple and straightforward solution using your sample Excel file.

multiple sheets.zip

All comments

afv2688

Hello @grossal,

This macro reads all the sheets or the ones you specifies and unions them all

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Read_excel.zip

1737280

Here's a simple and straightforward solution using your sample Excel file.

multiple sheets.zip

grossal

Thanks a lot @1737280 !

@afv2688 Macro works great and helped me with a different issue, but your configuration is just perfect. I was looking exactly for this!

I am still a bit confused by the naming. "Modify SQL Query" indicates something different to me.

NJT

For some reason I couldn't find this solution when searching import sheet names and so I came up with my own solution, not as nice as the macro or other solutions but it works. Using the input tool and importing the list of sheet names, then the dynamic input tool to read in the sheets and setting the template input to add the Full File Path to the file output. From there a formula tool to just pull the sheet name from the end of the full file path and Bob's your uncle.

Annotation 2020-03-26 121551.png Annotation 2020-03-26 121153.png Annotation 2020-03-26 121742.png

machadilip

Thanks , That looks great.

with the same approach , when I try with my excel inputs and I am not getting as expected. It was reading only sheet names and data is not pulling up along with sheet name

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703