Smooth Combination of Multiple Excel Workbooks with Multiple Sheets (Diff Names/Schemes)

[shaantrika]

Hi there,

I am writing this post today as I wanted to figure out the simplest way to combine workbooks in Alteryx in the way I specifically am looking to combine them.

I've pored through many different Alteryx discussion forum posts related to this issue but could not find a solution that I fully understood or could implement for my simple purpose.

I have attached 5 different workbooks to this post. "Fruits", "Media", "Tasks Roles Descriptions", "Empty Workbook", and "Combined Workbook".

The goal is to take the Empty Workbook, and write each tab from each of the other 3 workbooks (Fruits, Media, and Tasks Roles Descriptions) into the Empty Workbook so that it is joined into one final combined workbook with all sheets from all workbooks, with the individual sheets labeled appropriately as they were originally labeled (keeping the overall combined worksheet name of "Empty Workbook").

What is the most simple, efficient way to automate this process? Can someone put together a simple workflow for me using what I've attached below?

The spreadsheet attached labeled "Combined Workbook" is what the data should look like after the correct Alteryx workflow is run using the other 4 spreadsheets.

Would really appreciate some help on this.

Thank you so much,

Shaan

[DanielG · Accepted solution]

@shaantrika -- do you mean to represent a pre-formatted workbook as the "Empty Workbook", because if not, there is no need to start with a completely blank workbook.  The output tool will build it on the fly.  However, you lose control of the structure in that tabs will be alphabetical (unless that has changed in newer versions...) and there will be no formatting.  No ability to set autofilters automatically, etc. 

I mocked something up which uses a preformatted excel template and have attached a few screenshots of it.  The first is the structure that I prefer to build.  The second is just what is in the text input.  (that data can come from anywhere, doesnt necessarily have to be a text input.  Anyways, I use the blob in and blob out to take the template and save it down with a new name and/or location.  Then I layer in the Count Records tool to drop the data that precedes it then "fake append" it to the next data set by de-selecting the count within the append tool.  In this example I also layered using the Block Until Done tool.  Depending on the number of tabs, you might need a lot of those.  Hope this helps even a bit.  😀  

[shaantrika]

@DanielG,

Thank you so much for your prompt reply. I was able to play around with the workflow you drew up for me and come to a specific solution for my needs.

Your reply was very helpful in figuring out how to use the block until done tool to effectively combine tabs together into one, bigger workbook.

I appreciate the help and your time!

Best regards,

Shaan