Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Import multiple Excel Sheets with sheet name as column

Highlighted
12 - Quasar

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.

 

 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
6 - Meteoroid

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

Highlighted
12 - Quasar

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.

Highlighted
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

 

 

Labels