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.  

[JosephSerpis]

Hi @Rahul_Analyst have you seen this article as you can take the example provide and update the sheet in the formula to report.

[Rahul_Analyst]

Hi @JosephSerpis ,

Thanks for the reply. 

I tried it, but did not work. I think reason could be having different col names. I am attaching the workflow. 

I think two batch macro can give us result if it is possible. By 1st workflow (batch macro), we can make column headers identical of all files then using 2nd workflow (batch macro) suggested in the article and in Youtube video. It might work. but how can we make batch macro for making columns identical is tricky.  

[atcodedog05]

Hi @Rahul_Analyst 

Try this macro out https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Hope this helps : )

[JosephSerpis]

Hi @Rahul_Analyst just a suggestion but can you not configure the batch macro to output based on postion as while you column headers are not identical if the data is in the same postion per file you can utlise that method.

[Rahul_Analyst]

@JosephSerpis , I tried this also, but do not know why this error I am getting. 

[atcodedog05]

Hi @Rahul_Analyst 

Did you try mine any updates there?

[Rahul_Analyst]

@atcodedog05 , here also those 3 ways are described which is in the video and the other article link. I could not find it helpful. 

[atcodedog05]

Hi @Rahul_Analyst 

Try the attached workflow. Point the directory to your desired location.

Hope this helps : )

[Rahul_Analyst]

@atcodedog05 , It is giving error. 

Reasons may be:

1) your workflow contains excel file (.xlsx); however, mine one is (.xls). And, with this version, I am unable to import sheet names in the attached macro. 

2) sheet name is mentioned in formula tool - "Sheet1" but in my Sample data it is Report. (This, I changed in my workflow, still I think 1 reason is creating issue). Let me try, by saving this file in (.xlsx) version and with your workflow.