Input Multiple Excel Files (Same Single Sheet Name) And Union AFTER Minor Workflow

Question

I have multiple Excel files, where I want to pull the same single sheet from each (ignoring other irrelevant sheets) - each being structured in the same way. Before doing a Union to create a single consolidated file, there is some minor data prep needed to structure each sheet appropriately. I have have created this single-file workflow to do that minor manipulation (simply creates more of a flat file structure by adding a new initial field based on a cell value, which will be needed once unioned with the multiple other files).

When I try to wildcard an input it appears that it inputs and unions the multiple files and then tries to run the workflow. What I am hoping to achieve (at least based on my current way of thinking) is to loop through to input, run the workflow, and only then union the files after they have been restructured by the workflow.

Any guidance?

Capture.PNG

gbangsboll · Answer

Thanks! This definitely makes sense, but is there a way to do the processing before the union? Reason I am leaning towards that approach is the initial processing is quite simple (e.g., all related to some extraneous top rows, creating a column based on A1 cell value, etc to create a nice clean dataset) whereas I feel like doing so after the union will require much more complicated logic to do that clean-up at each of the "union points" between the various files (where each file may have differing number of rows, despite being same structure).

rzdodson · Answer

If the files are stored in the same folder, I would do the following:

* Start with a Directory tool - this will allow you to get all of the file paths you are going to need to bring in the data from each worksheet.
* Add aa Formula tool next - this will allow you to add the referenced sheet as a suffix for your file name
* Create a batch macro that reads in those file names - after all of the batches are done, each worksheet's data will be unioned together which creates a singular data set for the remainder of your processing.

Will mock an example up for you and will edit this post shortly.

Edit: example attached. Let me know if that is what you are looking for, @gbangsboll

Solution.yxmd