Multiple Excels - Read and Load specific Sheet only

[anudeepy]

Hi Guys,

I have 140+ Excel files in a directory. In every file, a Sheet is common - "Data". I want to load every one of them and filter out Data sheet(s) and combine them chronologically as per date. (date is included in Excel file name). I'm new to alteryx, Please help me in solving this.

Thanks,

Anudeep.

[estherb47]

Hi @anudeepy 

You can use a wildcard in your Input tool configuration. Connect to one of the files, and choose the "Data" tab. Then change as much of the filename as needed to grab all of the files in the directory, by replacing text with an asterisk.

So, for all Excel files in a directory, that would be directory\*.xlsx

You can choose, in the output settings, to include the file name as well.

As long as all of the schema is the same, then the files will union one after another.

If the schema don't match, then I highly recommend the Wildcard xlsx tool from Crew Macros.

Let me know if that helps.

Cheers!

Esther

[danilang · Accepted solution]

Hi @anudeepy 

You can try something like the attached workflow

The key here is that the file path contains a wildcard (*.xlsx) to read all excel files and that you specify to output the file name as a field.  That will load your data and add the file name to each of the rows.

from there you can parse the file name to extract the date information

Dan

[anudeepy]

Thank you @Esther