Seen a lot of issues like this come up on the community but I've never tried to do it before. Nice to learn something new, but will need to continue this learning I think. Still feels very foreign
This one was very frustrating, but my solution works and scales to accommodate any number of workbooks. All one has to do is determine which file should be the template file (i.e., the schema to base the other workbooks on).
Here's another one of my solutions. I believe this solution is correct, whereas my previous solution was a bit hacky.
Spoiler
I quickly identified two problems with the Excel workbooks:
1. Fields may differ across workbooks.
2. Sheet names may differ across workbooks.
I decided to create two batch macros.
1. Batch Macro #1 iterates over the FileName field from the directory tool (input to the directory tool is requesting that Alteryx reads in all files that follow the format File*.xlsx and returns the full path (i.e., both the file name and the properly formatted sheet name).
2. Batch Macro #2 iterates over the full paths, auto configures the fields by name, and allows the output fields to change based upon the input. The result is a union of all of the files.
Batch Macro #1
1. Fields may differ across workbooks.
2. Sheet names may differ across workbooks.
I decided to create two batch macros.
1. Batch Macro #1 iterates over the FileName field from the directory tool (input to the directory tool is requesting that Alteryx reads in all files that follow the format File*.xlsx and returns the full path (i.e., both the file name and the properly formatted sheet name).
2. Batch Macro #2 iterates over the full paths, auto configures the fields by name, and allows the output fields to change based upon the input. The result is a union of all of the files.
Batch Macro #1
Batch Macro #2
Workflow
