Input multiple xlsx files and union them

[jcyc]

Hi,

I have multiple .xlsx files (around 5,000). I wanted to input them all into Alteryx and union them together. 

The different files have variations in column headers so the wildcard option for the input tool is a no go. 

Obviously, I don't want to create 5,000 input tools and union them all together. 

Is there a faster way to accomplish this?

[Claje · Accepted solution]

Hi,

Try using the attached Batch Macro.

Once you have downloaded the macro, you can insert it into your workflow by right clicking on the Canvas and choosing "Insert->Macro.." then you can browse to the location where you saved the macro.

This tool will basically go through a list of files (which you can get via the Directory Tool, or a manual list), and will read in every single file, unioning all of them.  This will also automatically configure by name, so if a column is the same in two files it will be loaded to the same location.

[jcyc]

Thank you Claje for the reply.

Question: When I run the workflow, I get these warnings sayng XYZ "file has a different number of fields than the 1st file in the set and will be skipped".

The result is that it just reads the 1st file. How do I solve this? 

[Claje]

Does that occur when you use the batch macro I attached? It shouldn't.

I was able to load 400,000+ records from 39 different spreadsheets with different fields without any issues.

[jcyc]

Apologies,

1st - I was opening your file as a workflow and then running it (shown below)

But then I realized I had to import the macro as you said (right click, insert macro). However, when I try to insert it, it gives me a "the file is not a valid macro"

I am running version 11.7 if that's anything relevant?

[Claje]

That is definitely relevant!

I've attached an updated copy of the macro that should be correctly configured for version 11.7 - the one I had originally sent over is for version 2018.3 which would cause you some challenges.

Give this a try and let me know if you keep encountering errors!

[jcyc]

Claje,

I was able to run the batch macro and it did union all of my files. However, the columns did not configure by name and did not combine like column headers.

Instead it just stacked the different files on top of one another. 

Here is my configuration for my Directory tool

Here is my configuration for the macro

Thanks again for the help.

[jcyc]

Claje,

I see what I did wrong. I checked the "first Row Contains Data" box. I unchecked it and got the results I needed.

You're absolutely amazing.

[Claje]

Ah! :)

I don't think I did very much testing of that check box, so its probably likely that the field sizes change between files, and that the macro as written doesn't test that well.

Some kind of dynamic select to normalize field types would probably improve that if you needed to include it.

[zuojing80]

Hi I have 90 excel files and need to union one of the tabs in the file together. I used a formula tool after the Directory tool to specify the sheet name. However, each file may have more or less columns. I used the Macro but only the first line from the first file is imported in. Any help will be appreciated!