Import multiple Excel Sheets with sheet name as column

[grossal]

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.

[afv2688 · Accepted solution]

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

[1737280 · Accepted solution]

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

[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. 

[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