Batch macro efficiency

[gregsherman]

Just a quick question about the batch macro.

I have about 33 files that im looping through and going through each sheet as well. I have one control parameter that replaces the entire filepath to the same file but a different sheet. This is being done by having a table being put into the control parameter with each row being a filepath with its sheet at the end. For example, if 3 files had sheets labelled 1-5, the table would have 15 rows being put into the control parameter.

I'm wondering what Alteryx does in this situation. Does it

1. open the file from row 1 and the sheet associated

2. run the workflow with this file

3. close the input file 

4. open the file from row 2 (the same file but different sheet)

5. run the workflow for this file

6. close the input file (the same file that was opened and closed already)

or does it (or can it):

1. open the file from row 1 

2. access the sheet and run the workflow

3. check if row 2 uses the same file

4. if so simply access the next sheet, if not close the previous file and open the new one

5. access the sheet and run the workflow

6. checks if row 3 uses the same file from row 2...

I'm asking because when I initially made my workflow in python, the bulk of the time was spent opening the actual (quite large) excel files. My workflow currently takes a while to run and im trying to optimize it by combining some of the files into one book, if that would even work. Thanks

[ChrisTX]

Each pass through the batch macro will be independent, so there's no intelligence to recognize that a file can stay open because the next Sheet is in the same file.

So your description of option A is correct:

3. close the input file 

4. open the file from row 2 (the same file but different sheet)

Not sure if this would work (depends on your business process / output needs)....

I've seen others mention the options under Interface Designer.  I haven't tried it, but I'm wondering  if you could process all of the Sheets in one file with one pass through the Macro, which "might" (using Interface Designer options) combine all Sheets into one output data stream.  Then in your main workflow divide the sheets back out (if you need to), and use the Data Cleansing tool with the option Remove Null Columns, to get rid of empty columns in each separate Sheet.

Chris

[gregsherman]

That's unfortunate. I'm not doing column or row based operations in the main workflow, it's essentially one big python module with Alteryx to help format it into a table after, so the sheets are not consistent with each other always. It would be great if there was a way to open a file, process all sheets then open the next file. I've tried dynamic input but that messes a lot of things up since my formats are not consistent. It would be ideal to union them and then do the operations, but I am trying to map values to a separate mapping file and I'm relying on cell locations and offsets, so these offsets would be greatly affected by doing something similar to dynamic input. Maybe I can keep track of the current row iteration until it hits the recorded amount of rows for each sheet.

I'm not sure what the interface designer method is, but I had to select "Auto configure by name" anyways since it had some different output schema. Could you elaborate or link another thread about this method? Thanks for confirming my question.

[ChrisTX]

In the batch macro, interface designer, did you check the box for 

  Output fields change based on macros' configuration or data input

I haven't tried these options.

What's the primary purpose of your batch macro?  does it perform business logic, or is it only for output formatting?

I don't have any saved links to related community posts.  Try a search for "Output fields change"

Chris

[gregsherman]

I haven't checked that because the output fields themselves won't change, but the typing changes for some of the files so I selected the second option to stop errors that don't apply. The macro performs business logic in a python module for each sheet of a file. Each sheet creates its own table to output and the output for all inputs is sent to one single table output. My goal is just to save time by not having to open the same large file every time to access each sheet. I thought that combining files so that they'd only have to open once for all sheets would be quicker, but if it's not possible in Alteryx then having many smaller files seems more efficient since I'd only open the required sheet in the file (but that's not feasible).

[ChrisTX]

Sorry I can't be more help.  Sometimes it really helps to understand more of the process, to possibly provide alternatives in Alteryx.  I know all I'm doing is asking questions.

When you say the output fields won't change, but the data types might... would you be able to force the same data type for each field?

Chris