Run each sheet from an excel file independently through the same workflow

Question

Hi All,

Need some help regarding a problem I am facing. I have already created a workflow which functions as intended, but I need the workflow to run for each sheet in an excel file independently (meaning to say that I cannot combine the data from all the sheets into one). Saw some posts regarding the usage of a batch macro but I can't seem to figure it out. Not too concerned about the output, it can write to a single sheet or append additional sheets to the existing file. Any help would be greatly appreciated, thanks!

Diederik_vanderharst · Answer

Hi @ni_chew , @OllieClarke refers to this construction, I remembered this little article about it. Mastering this, makes AYX life much easier.

https://knowledge.alteryx.com/index/s/article/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field-Schema-Batch-Macro-1583459839440

Here's some more tips though.
1. If your XLSXs have a fixed list of tab names, you can append that list of tab names to the file path:   [Fullpath] +"|||" + [sheetname]
2. If your XLSXs have a changing variety of tabnames, you can retrieve them by sending [Fullpath] +"|||<List if Sheet Names>" in to your freshly built batch macro. The Input tool will then retrieve the tabnames, even if you configured it to retrieve a sheet. If you check the box in the input tools config to output the filename, the next step is easy. In your Batch Macro you then put another (similar) batch macro to retrieve those tabs one by one, applying the method as explained under bullet point 1.
So in batches you retrieve the sheetnames per filepath, one filepath being a batch, then in batches you retrieve the sheet, the filepath + sheet being 1 batch.

Good luck!

OllieClarke · Answer

Hi @ni_chew

it does sound like a batch macro will be the solution here.
There's a nice introduction to batch macros here: https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Batch-Macros-1583461640393

There's also an interactive lesson on batch macros here: https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923

In your case you'll want a control parameter linked to the input data tool. You'll feed the sheet name into this control parameter, and then use an action tool to update the assigned sheet in the input data tool.

You could then append the output to an existing sheet, or output to different sheets. Away from my designer atm, so can't share a workflow, but I hope that helps.

Ollie