Each iteration of your command group produces a new table of results or CSV file of data and you want to combine all of them into one file.
Common but Risky Approaches
-
Option 1: Set a dynamic chain variable
- Limited scalability: Dynamic variables have a strict size limit. Even if your data fits now, it may hit the limit in the future as data grows.
- Silent failures: If the variable hits the size limit, the chain will not error out; it will simply truncate the data, leading to missing records.
- Extra processing: You still have to convert the final variable string back into a file to use it.
-
Option 2: Write each file to a Wdata table
- Slow performance: This requires multiple API calls with authentication for every single iteration, which significantly slows down chain execution.
- Complex retrieval: You cannot use the data immediately; you must run a query to join it all back together.
- Cleanup required: If you need to re-run the chain, you must build extra logic to delete or replace the old files in the table first.
-
Option 3: Write each file to a Workiva Spreadsheet
- Slow performance: Like the Wdata method, the repeated API calls inside a loop will cause long execution times.
- High risk of error: You must calculate the exact row number for every insertion; otherwise, you risk overwriting data or leaving gaps.
The Best Solution: Previous Stack Files Output
If the columns are always the same with the same headers, then the best solution will be to use the Tabular Transformations Stack Files command along with its built-in option for stacking across command group iterations.
Note: For this use case, make sure to use the "Tabular Transformations" Stack File, not the File Utilities one of the same name. This is important because the File Utilities one will not recognize and remove the column headers from files after the first one, whereas the Tabular Transformations one will.
When this command is in a command group and iteration is turned on, the "Previous Stack Files Output" variable option will appear under its own "Stack Files" section in the variable panel.
Use this variable followed by a comma and then the command containing the data you want to stack across iterations.
Note: It is vital that your chain goes through the stack files command on every iteration and does not skip it. If the chain does not, then any data stacked previous to that iteration will be lost and you will have data missing from the final file.
Any command in your chain after the command group can then use the Stack Files - "Stack Files Output" variable which will be the full file with all iterations.
Other Notes
- The file utilities stack file command is useful if you want to join data sets where column header is not required and it has the same "Previous Stack Files Output" option.
- The Handlebars "Render Text Template" and "Render File Template" commands have options named "Previous Rendered Text" and "Previous Rendered File" which operate in the same way as the "Previous Stack Files Output" option seen here and could be useful for sticking templates and/or JSON files together across iterations.
- It's ok if the new file to be stacked going into the stack files command has no results, as long as the column headers are there.