This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
on 03-24-201704:59 PM - edited on 03-11-201909:18 AM by SydneyF
One of the best things about Alteryx is the ability to read in multiple files very easily and automatically combine them into a single dataset. This becomes a bit trickier when dealing with files that have different schemas or Excel files with multiple tabs. Adding both multiple excel files with multiple tabs, and having the schema change within each tab takes it to another level.
If your tabs have the same Schema, the article you want to read is here.
The way to accomplish the task if the tabs have (or may have) different schemas (field names change depending on sheet) is to use nested batch macros. I've attached a sample workflow built in 11.0 that demonstrates the process.
In the main workflow pictured above, the Directory Tool input pulls in the file paths of all of the XLSX files in the directory you're pointing to (note you may need to redirect this tool in the sample to a directory on your machine).
Most of the magic happens in the macro pictured above. This macro takes the FullPath field and updates the main Input Data Tool to read the first file in the list from the Directory Input. It is configured to read the list of sheet names within that first file, but also to output the Full Path from the Input Data Tool. A new field is formatted in the formula for an acceptable full file path for an Excel file, including the desired sheet name. This final file path is passed into the 2nd macro as the control parameter.
The second macro is very simple. It takes the file path received from the first macro, updates the Input Data Tool, reads in that file and then passes it back to the first macro. It repeats this process once for each sheet in each of the files being passed from the Directory Input Tool.
Each batch macro holds the data until each batch is completed and combines it all into one large data set.
Note: the sample was created in 11.0 - it will not open in earlier versions.