how to consolidate multiple excel files into separate sheets in one file with Alteryx

[Jeka]

 I have 12 different CSV files with one tab/sheet on each file.  Each tab has different number & name of columns and rows.   I need to consolidate all 12 files into one excel file under respective tab/sheet (different tab with the original tab name).   Appreciate if anybody can help with this.  Thanks. 

[abacon · Accepted solution]

@Jeka Here is a way it can be done.

I have a macro (seen here: Dynamic CSV Input Tool ) that can take all the csv's from a directory and pass them into the data stream in one go. Next I built a batch macro that saves a new file within the folder the macro is saved that send each CSV's data to a new tab that removes null columns for the issues where the schemas are different.

This will do the job for you, but let me know if you have any questions on how it works or getting it setup.

Bacon

[Jeka · Accepted solution]

@abacon thank you.  It is a quite complicated WF.    I do need some help in setting it up, let me message you in the chat.

[kiedam26 · Accepted solution]

I solved similar use case with a simpler approach using a Batch Macro. The Control Parameter updates the full file path and inside the Macro, the input tool is set to output CSV file names as a field. The output tool writes each CSV file to a master Excel file, using the input file name to dynamically name each sheet. 
In the main workflow use a Directory tool to bring in all the CSV files, then pass the full path to the Macro which writes each one to a separate sheet in the master excel file.