Alteryx Designer Discussions

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

MERGING SEVERAL TABS TOGETHER - 100+

iamviraj13
8 - Asteroid

Hi All,

I am attaching a file that has around 5 tabs as an example to merge. I have tried using Dynamic Input and have had success in merging 20-30 tabs together.

However, just wanted to see how can I merge more than 100 tabs all together? Sometimes the Dynamic Input gives a Schema error.

Any solution would be appreciated. Thanks!

4 REPLIES 4
iCFO
8 - Asteroid

Have you looked into this post?

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

 

I also sometimes use VBA to do things like splitting tabs to separate files, renaming tabs or combining tabs of the same schema to simplify multiple tab management.

 

https://www.exceltip.com/cells-ranges-rows-and-columns-in-vba/consolidatemerge-multiple-worksheets-i...

Maskell_Rascal
13 - Pulsar

Hi @iamviraj13 

 

Here is how you can do it. 

 

You'll first want to read in your Excel File to include the Full Path as a field and also only read in a list of sheet names. 

Maskell_Rascal_0-1631824819321.png

 

The use a Formula tool to create updated FileNames that include the full path and the sheet name. 

Maskell_Rascal_1-1631824892204.png

 

This will then be fed into a batch macro that will read in and combine all the tabs. 

Maskell_Rascal_2-1631825110085.png

 

The macro itself is configured for the needs of the sample file you provided, so the template it uses starts the data import on row 9 but can be changed if needed. You can also include the Full Path or File Name as a field, so you know which tab the data is coming from. 

 

Maskell_Rascal_3-1631825282696.png

 

Attached is a zipped copy of the sample workflow and the macro for you to try out. 

 

Let me know if this works for you. 

 

Cheers!

Phil

iCFO
8 - Asteroid

Great macro solution @Maskell_Rascal ! I am definitely going to be using this myself as well.

iamviraj13
8 - Asteroid

Thanks, @Maskell_Rascal for the detailed explanation.

Labels