Creating an Iterative Workflow for Excel Files with the same Sheets

Question

Hello All!

I am a noob with Alteryx and having trouble discerning the difference between Iterative and Batch Macros and finding the proverbial "Idiots Guide" to them.  I'm sure my use case is extremely simple and I may be over thinking it, but here it goes:

I have a set of ~50 excel files with various sheets, only one of which is of interest and they are the same across all the Excel files in question.  I essentially need a for loop to iterate over each file, grab a specific sheet, apply transformation steps to that sheet, and store it in a Database.  The sheet is not structured into a tabular format.  I have set up my workflow and it works for a single file, but when I introduce more that's where things go sideways.  Below is a png of my current workflow:

This successfully recognizes the files I need, but appends the results of the spreadsheets which does nothing for me.  I have scoured the board and I cannot find anything that satisfies my use case.  Any help would be greatly appreciated.  I'm hoping someone has come up against this.

Thanks!

BenMoss · Accepted Answer

I would definitely use a batch macro in this case.

First of all, build the process for one file/sheet. You then need to add a control parameter (this is what makes it a batch macro), where the action is to overwrite the filename.

Now when I embed this in a workflow the user needs to generate the list of files which we need to pull from. The batch macro will then process and write one of these at a time.

I've attached an example to this post which should give you a nice guide to building your own batch macro to perform this transformation.

Ben