Reading Multiple excel Files (Only select few sheets from each file) from a folder

[yatish1164]

Dear Community,

I have this folder where multiple files are placed by a department each month. These files will be replaced with the latest files each month when they come in so I would essentially be using Alteryx to create something like a table for me that can be then written into a database. 

The challenges I have are:

1. Each file will be overwritten so I cannot use a specific template for a dynamic input tool

2 Each excel file will have different number of sheets. I just need to pick 4 sheets these files

I have tried the below approach but I am not sure if there is a better way to do this.

List of sheet Names in Files:

File A: FC Summary, Road, Air, Ship, abc, def

File B: FC Summary, Road, Air, Ship, 123, pqd, jec

File C: FC Summary, Road, Air, Ship, 789, 564, led

I am only interested in bringing in FC Summary, Road, Air, Ship and I have made sure that their field names match so that I can then do a union.

End result: FC summary of all the different files will be unionid and written to a table in the Database

Road from all the different files will also be unionid together and go into a table

and same happens for Air and Ship.

Any help is highly appreciated! 

 

[mceleavey · Accepted solution]

Hi @yatish1164 ,

I've attached an example workflow along with the required tools to do exactly this.

Simply point the directory tool to the folder you want, and the naming scheme you want.

Hope this helps,

M.

[patrick_mcauliffe · Accepted solution]

@yatish1164  it looks like you need batch macros.  You're on the right track getting the list of names first.  Now, you just need to have those run like independent inputs... which is why we use a batch macro.

Here's how:

Set up a regular input like you would for any one of the Excel sheets.

I also like to output the file name so I know where it came from (option 5 on Data Input)

In the Interface tools, grab a Control Parameter and add that to the canvas.

Connect the Control Param to the Data Input and it will automatically add the Update tool.

Select the full file path, including the sheet within the Update tool.

Now, if you want to output to the database at this point, you can.  Or, you can pull the data back into the workflow.

Assuming you'd still want to do some work on this data, add a Macro Output.  Then press  Ctrl + Alt + D.  This will bring up the Designer Interface.

To avoid errors from mismatched schemas, on the Designer Interface, left side, tap the gear icon.

We want the macro to be set to anticipate the changing schema, so as to wait and union the data by name (of whatever other configuration you need).

Before saving the macro, I generally rename the Control Parameter Input something less generic

Now, save the Macro and go back to the canvas where you were outputting the list of sheet names.

Add a formula so that you have a field named for the Control Param input (like I have above with "Full Path") and it includes the full path of the file.

I usually do this by setting option number 5 to Full Path, like this:

So, very simply, to get the full path with sheet name, my formula only needs to replace the "list of sheet names" reference.

And that's what you feed into the new macro.

[yatish1164]

Thank you @patrick_mcauliffe and @mceleavey. Made my first batch macro today! I feel like an expert already 😄

[mceleavey]

[atcodedog05]

Great in-depth explanation @patrick_mcauliffe 🙂 👍

Congrats @yatish1164 on building your first batch Macro 😀 🎉🎉

And nice to see you after a long time @mceleavey 😎

[mceleavey]

Busy times, @atcodedog05 .

Hopefully see you at the next user group on November 5th 🙂