Combine Multiple excel files with different schema

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.

Sample1.xls

Sample2.xls

Workflow

Input

Batch Macro

Help

Accepted answers

RolandSchubert

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

Union Sample file.yxzp

All comments

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.

Union Sample file.yxzp

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.

RolandSchubert

Hi @Rahul_Analyst ,

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

Best,

Roland

Union Sample file.yxzp

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.

Union Sample file.yxzp

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703