Combine Multiple excel files with different schema

[Rahul_Analyst]

Hi,

I want to union 10-15 excel files with the help of Alteryx. However, facing difficulty because of having different schema. I thought a way first to make my data in same data structure or same columns name with the help of a batch macro. Then union it with another batch macro. However, facing difficulty in it. I attach my sample 1 and 2 data. If you can give a try, how it can be possible even if we have different data structure or col headers, still we can union it. I need to union only first sheet (Report) in my sample data. 

I tried to search a way of doing it and found this video on Youtube where his 3rd example can resolve my issue. However do not why it is not getting done. 

https://www.youtube.com/watch?v=MjGxzsMJINQ

Thank you.  

[atcodedog05]

Hi @Rahul_Analyst 

Yes please do try with .xlsx .xls is a older version and lot of features are not supported.

Hope this helps : )

[RolandSchubert · Accepted solution]

Hi @Rahul_Analyst ,

I modified your workflow and the macro a bit:
- additional filter for "*.xls" files
- created the file+sheet name as filename + "|'Report$'" to point to the sheet name

In the macro, the Action tool missed the "Replace a specific String" setting. In addition, I switched to "autoconfigure by position" in interface designer.

Using the sample data you provided, it seems to works on my PC, let me know if it works for you.

Best,

Roland 

[Rahul_Analyst]

@atcodedog05 ,

I changed file type to .xlsx then I think macro needs to be configured in such a way, so it should point only to "Report" sheet and union it. It should leave other 3 sheets which are there in sample data. 

A suggestion:- can we not create first a batch macro to clean the data and make all the files in same schema then using this another batch macro for union it ?

[Rahul_Analyst]

@RolandSchubert ,

I wonder how it worked for you but for me this error is coming. 

But one thing- when I added $ in formula tool after Report then it fetch the data for first file and did not give error but did not union 2nd file. 

[Rahul_Analyst]

@RolandSchubert 

Hi, it worked. 

Actually, I was not using browse tool to see the union result. Without browse tool or output tool, it was showing me only first file data that is why I was thinking it is not giving me correct result. But the moment, I added browse tool, then I could see the data from both the files in result window. 

Thanks a lot, @JosephSerpis , @atcodedog05 , @RolandSchubert  for your time and responses. 

[Hi2023]

Hi, 

I am trying to do the similar to my workflow.

I have 5 files with 5 sheets and I need to join all 5 files together but only certain sheets from within the 5 files and also 2 sheets do not have unique identifier but still need to be joined together with all. I could not open your workflow -newer version. :/

I have tried Batch macro but got an error on last macro and got stuck. 

So there is a way to create a batch macro to clean the data then union it?