I have a client that provides a monthly snapshot of information in an XLSX file. When the data in those files are combined, it paints a historical picture of how data is changing over time that is great grist for an analytics mill.
The client process that generates a new XLSX file each month creates files with “different schema”, however, outside of my control. How do I know this? Because Alteryx complains when I try to read them all in at once using an Input Tool with a wildcard in the “Connect a File or Database” field. The Dynamic Input Tool also fails when the XLSX files have different schema.
So does that mean each month I have to remember to manually update my Alteryx workflow to include 1 more Input File tied into a Union Tool, and then make sure nothing else has changed with any of the other filenames that will make the workflow doesn’t break?
There’s a better way. It turns out that by using a super simple macro, I can read in all the XLSX files in my folder and create a Union among them, even when they have different schema.
To walkthrough the step-by-step solution , check out this excellent post in the Alteryx Community by JordanB at Alteryx entitled “Reading in multiple files with different Field Schema – Batch Macro”: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-different-Field/ta-p/13370