community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

load multiple excel files from a folder

Meteoroid

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.

Asteroid

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

 

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

Quasar

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

 

wildcard input.PNG

 

 

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

 

directory input.PNGYou 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.

 

formula for sheet.PNG

 

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.

Meteoroid
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.
Quasar
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.
Asteroid

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

Pulsar

@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

Meteoroid

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

Atom

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

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

 

 

Labels