community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

Combine Multiple Excel Workbooks into One Workbook

Atom

I have tried to read in the community about possible solutions to my issue, but the differentiator here is that my sheets are different unlike the other posts in the community. So I have one Excel Workbook with 30 tabs and then 10 Workbooks with 1 tab each. I want to use Alteryx to combine my 11 total workbooks into one functional workbook with 41 tabs. Can anyone help provide guidance on how to combine these workbooks into one singular one?

Community Operations Manager
Community Operations Manager

@riship3,

 

This article about halfway down talks about a batch macro that can assist with your needs. You can build the macro to ignore the schema of your worksheets and have the macro look at the field names or use the position of the fields to bring in the data. 

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

 

If your data does not meet any of the three criteria (same schema, same field names, same position) you will have to use Input Tools for each type of schema or build multiple macros such as one that looks at field name and other position. You can then use a Union tool to bring all of that data together.

 

Cheers,

DanM

You might get some good tips from this article here - I believe in this case with multiple tabs & files, a batch macro is the way to go. 

 

Essentially, for the document that has multiple tabs, you will select that file in your Input Tool but then instead of selecting a single sheet name, you will bring in the "<List of Sheet Names>" for that file. You can then use a formula to concatenate the full file path with each tab name, like this: Filename.xlsx|||TabName

 

Once you have your list of file names (to which you can also add a list of the file paths + tab names for each of the individual files), you can feed them into a batch macro where each file/tab will get input one at a time and output to a tab in your combined file before processing the next file. This helps get you around the whole "different schema" thing. 

 

If you need additional guidance, please let us know! There are probably more than a few Community members out there who have macros... @MarqueeCrew can probably tell you a few different methods, including one of the CReW macros that might work, the Wildcard Input tool? 

 

Hope that helps get you started!

 

Cheers,

NJ

Atom

Hmm, okay I will look into that. Thanks for the help! I'm assuming there is no way to do this without the use of a macro?

Atom

I was able to do it by using Data Inputs and selecting each individual sheet and outputting it to a new workbook. Thus, having each sheet sent over to a new Excel file. The only issue with this is that I lost source formatting and formulas. If anyone knows how to resolve that aspect of it, then this solution works.

Labels