How to get data from a folder with excel files

I have a folder with 5 excel files with different names but the exact same structure. I am trying to use the directory tool combined with the dynamic input tool to go to my folder, go to a specific sheet called "Raw Data" in each workbook and essentially stack the data in all 5 files on top of each other. I also want to add a column that contains the file path of each workbook.

In the directory tool, for "directory" I just have my folder path. For "File specification" I just have *.*

In the dynamic input tool, for "input data source template" I just chose one of the 5 files in my folder. Then for "field" I chose FullPath and "action" I chose Change File/Table Name

I keep getting an error saying that one of my 5 files does not match a sheet or named range in a different one of my 5 files. I've check the data and all my workbooks have the exact same structure.

Any help is very appreciated!

Error Message

Accepted answers

BenMoss

If the schema is EXACTLY the same, then you can use the following method.

Firstly, get an input data tool, and select just one file and the raw data sheet.

This should give the file name in the top of the configuration panel and the sheet name in option 3.

Now, amend the string, replacing the filename with an *, this is a wildcard.

So in my instance the string

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\Test.xlsx

would change too...

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\*.xlsx

Now, when you hit run, Alteryx will find the first xlsx file in that directory and pull it in (given the raw data tab), and then repeat this for all files, automatically stacking the data on top of each other.

When using this method, I would also change option 5, and output the filename as a field.

Also keep an eye out for the results pain, this will tell you if any files don't match the required schema, which is defined by the first table.

Also, lets say you have other files in your directory, then this method would require some more specific detail in the filename, such as a filename suffix that only exists on the files you want to bring in, for example

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\salesdata_*.xlsx

will bring in only excel files with the prefix salesdata_

If your files do not have exactly the same schema then you should consider using the macro in this blog...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-different-Field/ta-p/13370

Ben

All comments

BenMoss

If the schema is EXACTLY the same, then you can use the following method.

Firstly, get an input data tool, and select just one file and the raw data sheet.

This should give the file name in the top of the configuration panel and the sheet name in option 3.

Now, amend the string, replacing the filename with an *, this is a wildcard.

So in my instance the string

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\Test.xlsx

would change too...

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\*.xlsx

C:\Users\Benja\Desktop\Hide\MultipleInputsandOutputs\salesdata_*.xlsx

andrewchen9

worked perfectly, thanks Ben!

Krystophe

Does this only work with CSV files ?

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335