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.

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

[neilgallen]

There is a "Include filename as field" checkbox in the input data tool with two options

1) include full path

2) include file name only

This will create a new field in your data that provides the filename of the source file. This works across both batch macros and if you use the input tool with a wildcard.

[Andrzej]

Hi @kcgreen 

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

[kcgreen]

Hi @Andrzej 

For multiple CSV files, I typically use an asterisk or two as wildcards in the file name of a file name.

For example, configuring an Input Data tool to connect to:

C:\Users\kevingreen\Documents\*Matched*.csv

will return all CSV files in my Documents folder with "Matched" in the file name

I think this may only work if the files are formatted the same.

This thread may be of use to you:

https://community.alteryx.com/t5/Alteryx-Designer/Reading-multiple-CSV-files-at-once/ta-p/38221

[trettelap]

Any way to do this when the tab names are slightly different per file? Schema would be the same file over file...thinking this can be a macro but would rather not use if possible. 

[neilgallen]

If the sheet names are different across the excel files, then the most foolproof method is to take advantage of the "read a list of sheet names" option within an input tool.

Use either a wildcard or the directory tool to ensure that you get every file in the folder, and when prompted, choose the sheet name option on the input tool. Also ensure that you have the "Output File Name as Field" option set to 'Full path'.

After the input tool, the easiest route is a formula using the replace function to swap out the query string for the sheet name with something like:

Replace([FileName],"<List of Sheet Names>",[Sheet Names])

At that point you should be able to push to a dynamic input tool and not need a macro.

If you need a workflow mocked up just say so. Good luck!

Doing it this way ensures that if a sheet name changes you will be able to adapt.

[trettelap]

so i was able to get it to work with a directory and the dynamic input with list of sheet names and change entire file path...I would be interested in how you workflow works though...

[neilgallen]

not sure what you're asking?

In my scenario I have a number of files that have a differing number of sheets within them, each appended with a number ("Page_1","Page_2", etc). However, the data on each sheet is the same schema. It's split due to row limits on excel files.

I use the method outlined in order to ensure we get every sheet for each file, and then union the entire dataset. 

How else can I help?

[tchacha]

@neilgallen thank you for posting. I have a related question on loading multiple excel files. I am trying to complete the Weekly Challenge #19 (https://community.alteryx.com/t5/Weekly-Challenge/Challenge-19-Excel-Record-Locator/td-p/36748) but am having trouble connecting the input tool to the file path that contains the multiple excel files I am trying to read in. I am following the guidelines of using the file path and replacing the common naming of the excel files with the wild card "*". However, I keep running into a couple error messages "Root element is missing" and a "Windows Error: Access is denied". Have you ever run into these errors before?

[AS]

after this how we could use and read the files columns further. Could you please explain