load multiple excel files from a folder

[Himal]

Hi,

I am trying to build a macro to load multiple excel files from a folder. It would be great if there is a sample workflow and apps in the designer, to simulate the data.

Thanks.

[CiaranA]

Do all the excel files have the same schema? columns etc.

and you just want to join them on top of each other?

[neilgallen · Accepted solution]

@Himal If your files are all of the same naming structure and schema, you do not need a macro. You can use the input tool with a wildcard. The example below would find all excel files in the folder that begin with "File". Note that the sheet name for every file must also be the same.

If the file names are different, then you would need to use a directory tool to gather a list of all your files.

You would then pair this with the dynamic input tool in order to bring in your files. In this situation, the schema must also match. Since you are using excel files, you would also need to include the sheet name in your file path by including a formula tool. Alteryx needs to know which sheet in the excel workbook to reference in the input.

If things differ further than that, then you would need a batch macro, which I can detail out if these solutions do not meet your needs.

[gurth]

I've followed this word for word, but get error message "Formula (4) The field "" is not contained in the record. (Expression #1)". I'm trying to do Challenge 19, but I can't seem to find the way in which you can use one input tool span multiple files! Please help me.

[neilgallen]

Can you attach your workflow? It would be much easier to diagnose the issues if we can see what’s going on. Most likely it’s related to the field names in your formula tool.

[kcgreen · Accepted solution]

The Read All Excel Macro does this.  This is one of my favorites.

Here's a community write up on it:

https://community.alteryx.com/t5/Engine-Works-Blog/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-AL...

Here's the macro in the Gallery

https://gallery.alteryx.com/#!app/Read_All_Excel_Files/58dd51b3a18e9e18fca64172

[estherb47]

@gurth , that particular error tends to happen when you create a new field with the formula tool and forget to give it a name. I see that one often in my workflows :)

Cheers!

Esther

[gurth]

It was a technical glitch at my end! Just ignore me!

[aao13]

What will be the next steps on how to combine data from different workbooks provided that they have the same columns and field names. thanks

[Lakshmi11]

I tried using this macro to combine multiple xlsx excel files however the following error. please help