Browse a folder with multiple files with same column names, generate a single file output

Question

Hey everyone,

My scenario: I'm trying to use the "Folder Browse" tool to allow user's choice for folder selection, and the folder will have multiple excel files say input1, input2, input3 where all these files have same columns say ID, Name, Contact, Result. I want to perform a specific task let's say "sort by descending" on the data from all these 3 input files, perhaps in a loop, and finally generate a single output file with all the data combined and summarized in a way that the 3 files data can be differentiated in that final excel output file.

Can someone help me with creating such a scenario?

Input1.xlsx

MattBSlalom · Accepted Answer

Since you're using the Folder Browse tool, you're inherently creating an App vs a standard Workflow (just want to call that out for others that may come across this post).

The Input Data tool handles this scenario beautifully since you specified that all the Excel files have the same structure (fields).  We need to configure the Input Tool's path to point to any Excel file rather that a specific name, so update the file name portion of the path to use a wildcard like "*.xlsx".  Also update the "Output File Name as Field" option on the Input Tool to be "File Name Only" or "Full Path" (whichever you'd rather see in your results).

Then use an Action Tool between the Folder Browse & Input Tool to replace just the folder path portion of the File path on the Input Tool (leaving the wildcard file name & sheet name as configured directly in the Input Tool).

The Input tool will read the data (all in one pass, no looping) from all files matching the wildcard file name with the specified sheet name in the user provider folder path.  Then just add your desired transforms (like a Sort), and Output the results to your desired location.