Combining multiple excels into one workbook with multiple tabs.

[Anjitha3913]

I have 20 excel workbooks in a single folder. The name of each excel is in the same format with a specific identifier. Now i need to combine all the excels into one workbook with 20 tabs. All the 20 files has single tab and the name of the tab is same across all these files. Also, i need to rename the tabs in the final output using the specific identifier in the file name. Been reading about directory input and dynamic input; can't seem to get those solutions to work. Can someone help me with a solution for this.

[RolandSchubert · Accepted solution]

Hi @Anjitha3913 ,

is the structure of all sheets identical(e.g. same columns , column headers)? You can use the Directory tool, Dynamic Input tool and Output Data tool, but will need a few formulas to create the right names. I've attached a sample workflow and test files.

Let me know if it works for you.

Best,

Roland   

[Anjitha3913]

Hi @RolandSchubert 

Thanks for your help on my query. I saw your result and this is exactly what i require. But do you have a solution in case my files have different schema, as its difficult to identify what is exactly different when the error on different schema pops up. I also tried a batch macro reading another article (i am very new to macros). 

Error without using batch macro : "fullpath///sheet name" has a different schema than the 1st file in the set.

Error when using batch macro : Record #: Tool #5: The field schema for the output "Output 5" changed between iterations.

Out of my 20 inputs except for 2 files, these errors are poping up.

Regards,

Anjitha

[grazitti_sapna · Accepted solution]

Hi @Anjitha3913,

If your files are having different schemas then please set the macro to Auto Configure by Name or position in the Interface Designer, then it won't error out.

NOTE: Sheets within the same files should have the same schema.

I hope this helps.

[Anjitha3913]

Thanks @grazitti_sapna & @RolandSchubert for your solutions. Really helped me to go ahead with my workflow.

[Anjitha3913]

@RolandSchubert 

Could you please give more explanation on certain areas of your workflow which i am not so clear about?

1.  Formula used add sheet name : I didn't understand the expression used before "Sheet 1"
2.  In the output from Dynamic Input, along with the data from the input files, how do we get the File Name? Since in my workflow i am using Batch Macro instead of Dynamic Input, is there a way to get the File Name similar to your solution?

Please see attached screenshots from your workflow where i have highlighted my areas of doubt.

Appreciate your time.