Repeating Same Workflow Through Multiple Files Inside Same Folder and Union

[Max_Lam]

Hi, I need to run multiple files in the same folder through the same workflow logic.

All the files have the same schema. I have attached two excel sample files as reference.

Basically I was just adding an extra column of month-year data to each file based on the month of release.

After adding the month-year column in each file I will then use Union tool to merge all files together cumulatively.

I have heard that I should try batch macro or use the wild card *.xlsx in input data, but I am not sure which one would be best.

Thank you!

[AngelosPachis]

Hi @Max_Lam ,

If all the files in your folder are of the same schema and the sheet names are exactly the same, then the best solution in my opinion would be the wildcard union (*.xlsx) which will union all files in that directory.

By the looks of it, a wildcard would work, just make sure that you skip the first 3 rows in each file if you don't need them cause they may mess with the looks of your table, it's up to you.

If the schema is different, then you will receive a relevant warning and that means that you should build a batch macro to bring all your files together.

Hope that helps,

Angelos

[mceleavey]

@Max_Lam ,

In addition to @AngelosPachis 's method I've attached a method that dynamically loads in each file using the directory tool. This allows you to read in a list of all records in the folder and sub-folders, then specify the individual tabs you want for each file (or all of them by default). It then loops through and loads them all in using the macros attached.

I've attached the workflow example and the two tools, one which loads in all sheet names and one which loads all the files.

Hope this helps.

M.

[Max_Lam]

Hi Angelos,

Thank you very much for your quick response.

Hi Mceleavey,

Thank you very much for providing the attached files!

Sorry I am still very new to Alteryx and I think I might need some time to digest your workflows and macros.

I will get back to you if I have any further inquiry.

Thanks!

Max

[mceleavey]

@Max_Lam ,

to install macros I would suggest opening them up in Alteryx then saving them to your macro folder.

To set a macro folder go to Options->User Settings->Edit User Settings:

Here, click the plus sign and navigate to the folder you wish to use for your macros.

Save your macros to that folder and away you go. Those macros should now appear in your "In/Out" section:

M.