Merging multiple files together with different number of columns.

Currently I am using a batch macro process to combine files on top of each other, however each file has a different amount of columns, and my batch process only looks at matching the same column in the same space each time. Files are continuously added to the folder every day, so the merged file is updated 2x a day through the gallery.

So when the batch process runs on my files, the macro doesn’t see the same column titles to stack on top of each other, so it ends up creating new columns.

I would like to stack the same columns on top of each other and be able to add any columns that are new in the merged file.

I have attached some sample data for clarity.

Sample1.xlsx

Sample2.xlsx

Batch Macro

Accepted answers

apathetichell

Try this one...

@rkapoor- updated version which spits out filename and allows for .csvs to be read in...

Excel-lent adventure 2.0.yxzp

All comments

rkapoor

@AshaCharis I was testing out a macro built by @apathetichell on your Excel sheets and I think it produces what you want as attached.

AshaCharis.yxzp

atcodedog05

Hi @apathetichell ,

You should publish this on Alteryx public gallery 🙂

apathetichell

@atcodedog05 - Done ! - thanks for the support @rkapoor.

AshaCharis

Thank you so much for your help.

The files that are coming in are in .csv; do I just need to create another empty file sample the batch off of?

apathetichell

oh - that is a totally different project and a bit easier.... My macro is for different schema .xlsx/.xls - try the attached for .csv

2021-07-26 csv reader.yxzp

apathetichell

oh - did you want a combo macro which reads in any of .xlsx,.xls or .csv? It's possible but I haven't done it yet

AshaCharis

In the macro I wanted it to do is to stack the same columns on top of each other and be able to add any columns that are new in the merged file, but the macro that you first provided seems to not like the fact that I am stacking csvs.

apathetichell

Try this one...

@rkapoor- updated version which spits out filename and allows for .csvs to be read in...

Excel-lent adventure 2.0.yxzp

rkapoor

Thanks for that @apathetichell

AshaCharis

Thank you so much for all of your diligent help! I am going to run it through my private gallery a couple of times to make sure I don't see anything weird on my end.

But it flows nicely!

apathetichell

Glad to hear - it's still a bit of a WIP and all the R environments are a bit different.

JamesGray

Hi I believe this is the workflow I am looking for to help with my issue.

I have monthly payroll reports each in a separate xlsx file. They have the same named columns but some have more columns than others. I would like to union them all into 1 document (ie. to have the annual payroll report).

Will this workflow match the columns from each file even if in different column positions?

When I try to run the workflow I am told that it is made on a newer version of alteryx and therefore I cannot open it. I am using Alteryx version 2019.3.1.24384. Is it possible for you to upload a compatible verison?

Thank you

apathetichell

@JamesGray-The parts of the macro which might break on your Version would be related to the R - which isn't needed for .xlsx file combo. I won't have time to make any changes until this afternoon - but you can probably cut out 2/3rs of the file I uploaded. You'd only need:

batch macro 1 - read in .xlsx file list of sheet names. prepare full file paths based upon sheet name + file name. macro 2 - read in file path/sheet path from macro 1. - output data unioned.

Everything else in the macros I attached you can delete for your use case (and that's most likely the part which wouldn't work in 2019.3.

This assumes that you are only using .xlsx and you can put them all in a directory and feed the macro via a directory tool. You feed in full path to macro 1 and the fullpath + sheet name to macro 2.

If you are dealing with .xls files - we'll have to talk about R versions and that's a whole different conversation.

JamesGray

Thank you for getting back to me so quick. My main problem I am having is that I get this error message when I go to import the file. When I click Ok no workflow loads up. I don't know if it is possible for you when you have time to save it as an older compatible workflow?

Only using xlsx file types

Thanks

apathetichell

click yes. nothing will open. now go to browse. you should see it as an uncompressed zip folder inn your downloads directory.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703