Consolidate all the excel files in the folder to new excel file per sheet.

[RichardAlt]

Hi Guys, 

Please help me to my project, How can I consolidate the excel files in folder consist of 3-4 files to new excel file per sheet/tab, What do I mean per sheet? For ex. I have 3-4 files in the folder (Sometimes 3, Sometimes 4) if I consolidate that 3-4 files into new excel the new excel must have also a 3-4 sheets/tabs. 1 excel file = 1 sheet/tab to new excel file.

I have used a sample macro that I got it here, But the macro is consolidating the all files into one excel file only. Please check the attached zip file.

And also, please see my Master File. Master File is my desired output.

[danilang]

Hi @RichardAlt 

I've modified the macro so that it writes all the information from each of the files to a different sheet in the output file.  Each sheet is given the filename of file from which the data was read.  The writing needs to be done in the macro since once all the data is combined together with multiple columns it becomes difficult to separate out which columns should be written to the correct tab.  The output of the macro now gives a summary of how many records written to each sheet.

The main workflow now has a Text Input tool to enter the name and path of the output file.

Dan

[echuong1 · Accepted solution]

See attached for a workflow you may be able to leverage - you were correct in that a batch macro is needed. Essentially, you need to compile a list of the files and their corresponding sheets. From there, that is used as a parameter within a batch macro to dynamically import each. Within the batch macro, you can use that sheet name to create the new path and write out with each iteration.

You will need to update the workflow in two places:

• In the overall workflow, you'll need to adjust the input data tool to have your folder path 
  • C:\Users\erica.chuong\Downloads\New folder (5)\*.xlsx
• In the batch macro, you'll need to adjust the formula tool to contain your desired file path and file name
  • 'C:\Users\erica.chuong\OneDrive - alteryx.com\Desktop\Sample Output.xlsx|||'
    +
    [FileName]

Let me know if this works for you!

[RichardAlt]

Hi @echuong1 ,

Thank you for the answer but I can't run the workflow, Please see the attached file.

[echuong1]

The batch macro should have been packaged with the workflow, but it looks like it's not recognizing it correctly. 

I've attached the batch macro here. Delete the black tool with a question mark and replace it with this macro. Within the macro configuration, select the FileName field.

[RichardAlt]

Only the information file is my output.

Please see attached image.

[echuong1]

Did you ensure the wildcard (*) is present in the input file path instead of a specific file name as specified? 

Your input tool should output four lines - one for each file and sheet. I assume yours only has one.

[RichardAlt]

Yes, I already change it to wildcard. 

[echuong1]

Have you set the control parameter to be the FileName field? This is what it will use to batch through the inputs. 

Within the Action tool in the Batch macro, ensure the highlighted section is the File - Value portion and at the bottom "Replace a specific string" is selected. The text box should have the entire value present in the highlighted section as shown in the screenshot below.

[RichardAlt]

Thank you! It's working now! I've just forgot something.

But what if I don't have the information.xlsx file? It would run?

And also, How can I add the batch macro tool? Because I just copied the tool from the other workflow, I don't know how to add it.

Please see the highlighted tool.