load multiple excel files from a folder

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.

Workflow

Developer Tools

Reporting

Output

Batch Macro

Preparation

Interface Tools

Run Command

Iterative Macro

Accepted answers

neilgallen

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

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.

kcgreen

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-ALL-Excel-Macro/ba-p/9988

Here's the macro in the Gallery

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

All comments

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

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.

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

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.

kcgreen

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-ALL-Excel-Macro/ba-p/9988

Here's the macro in the Gallery

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

unknown

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

Utkarshaggarwal

Thanks for much for sharing this solution. I need to know one additional thing. How do I add the file's name while importing the data in this case?

I have multiple excel files in the same folder. Each file has 2 sheets each with the same name & structure. I am using the formula tool as you suggested entering the sheet's name. I want to include the file's name as well in the collated data so I can track what data came from which file. Please help.

Andrzej

Hi @kcgreen

Do you know some macro, which will read all excels and csv files? this one reads only excels

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335