Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

2022-05-24 Updates: Login: If you are facing issues with logging in, please try clearing all your cookies or trying another browser. We have a fix being deployed on the evening of 5/25. Email: If you're not seeing emails be delivered from the Community, please check your spam and mark the Community emails as not junk. Thank you for your patience.

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