Merging multiple files together with different number of columns.

[AshaCharis]

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. 

[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.