Run workflow for every tab

[scp]

Hi,

First off, I'm new to alteryx and I've searched the community for this and found bits and pieces, but nothing that I can use directly so far. I appreciate any assistance. 

I have a singular xlsx file that has many tabs of unknowable tab names. My goal is to aggregate the data on the tabs together, however, the tabs are organized by row with irrelevant tables and such to ignore, and an unknown number of columns of unknown column headers. To make order of an individual tab, I've created a workflow that uses a singular xlsx tab and a static helper file to dynamically change/un-pivot/filter/etc to make a standard output ("SingleWorkflow"). I would like to run this workflow on each tab in the xlsx file. At present, SingleWorkflow assumes it is passed a column with the full path, and uses this to parse both the filename and the tab name into new columns which are included in the output.

I successfully converted SingleWorkflow into a macro that works passing it any particular tab, but it works on only 1 tab at a time. I would like to run SingleWorkflow on each tab individually, for every tab in the xlsx. 

Any xlsx file may have 50+ tabs. There are ~100 files to do this for. Below is a demonstration graphic of manually using 2 input tabs. 

The output needs to include filenames and tab names so I can eventually aggregate together across those ~100 files and still know where the data came from. File and tab name columns don't need to be made inside SingleWorkflow if there's a better way. I just need to associate an input tab with a filename and a tabname for tracing when this is merged with all the other files (which will have repeating tab names). Luckily, filenames are actually unique. 

Lastly, I've looked into dynamic input, but this is aggregating all tabs together up front without processing each tab. The data is unusable in this case as there's no way to identify which rows came from which tabs. 

Again, thank you for any assistance!

[Martyn]

I've had to do something similar with just a single spreadsheet file with multiple tabs in it - but it's easily adjusted to do for multiple files as well.

You can use an Input tool to provide a list of tabs in each spreadsheet file within a folder - and then use this list as an input into a batch macro (the macro will then run for each tab). Set your filename to have a wildcard - *.xlsx and then configure as follows.

Please note that it is a batch macro that you want to perform - even though it seems that you are iterating. A batch macro will run through once for each of the control values it is passed (filename and tab name in this instance) whereas an iterative macro will run multiple times against the same data until a condition is met.

Hope this helps.

[scp · Accepted solution]

Thank you Martyn!

I have looked into this and also while looking into dynamic input, stumbled on the solution that works for now. The below image is from the dynamic inputs / input data source template dialogue box. While this works across tabs in a workbook, my workflow does not yet work across all files in a directory (yet).

The dynamic input can be set just like the standard file input to append full path as a column. Prior to this, dynamic input can be passed an array of sources (full path with tab names) and it runs in its own loop. 

Since I'm new I'm probably not clarifying this correctly but here is what I think happens. 

1) Input tool gathers list of tab names

2) Intermediary steps to convert tab names into full paths with the tab names at the end

3) Pass modified list to dynamic input

4) re-sort from there and run batch macro, that is also passed the list of filenames (and it can use that to sort against on its own)

Jason