Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

load multiple excel files from a folder

Himal
6 - 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.

21 REPLIES 21
CiaranA
10 - Fireball

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

 

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

neilgallen
12 - 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.

gurth
7 - Meteor
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
12 - 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.
kcgreen
8 - 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

estherb47
15 - Aurora
15 - Aurora

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

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

aao13
5 - 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

Lakshmi11
5 - Atom

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

 

 

Labels
Top Solution Authors