6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Import multiple Excel Sheets with sheet name as column

grossal
14 - Magnetar
14 - Magnetar

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:

KeyValue
1A
2B
3C
4D

 

Sheet Videos:

KeyValue
5E
6F
7G
8H

 

Goal:

KeyValueCategory
1ABooks
2BBooks
3CBooks
4DBooks
5EVideos
6FVideos
7GVideos
8HVideos

 

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

 

 

You'll find a sample file attached.

 

 

 

 

5 REPLIES 5
afv2688
16 - Nebula
16 - Nebula

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
8 - Asteroid

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

grossal
14 - Magnetar
14 - Magnetar

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
11 - Bolide

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.pngAnnotation 2020-03-26 121153.pngAnnotation 2020-03-26 121742.png

 

 

machadilip
5 - Atom

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

Labels