KNOWLEDGE BASE

How to append multiple outputs to an Excel in a predefined order


Published: 21 Jun 2021
Last Modified Date: 22 Jun 2021

Question

When appending multiple outputs (more than 3) to an Excel file, the order of the Excel worksheets becomes random. For example, in this flow, there are 5 parallel steps, and in the output setting dialog, the "output type" is "Excel" and the "Full refresh" is "Append to table"
User-added image User-added image

When running this flow, the order of the worksheets in the output Excel becomes random, instead of DATA 1, DATA 2, DATA 3.......
User-added image

How to make the output steps execute by order or in a pre-defined order?

Environment

  • Tableau Prep Builder 2021.1.3
  • Windows 10
  • Excel

Answer

Option 1

Append the outputs to pre-created Excel worksheets
1. Create the output Excel file.
2. Create 5 empty worksheets and name the worksheets in the order which aligns with the order of the output.
User-added image
3. Create the Prep flow, select the corresponding re-created worksheet for each step in the output setting.
User-added image
4. Click "Run all flows" at the top bar

Option 2

Execute the flow steps by order
1. Create an output Excel file.
2. Create the Prep flow, set the output file location in each step to the pre-created Excel file.
3. Click "Run Flow" next to each step in the order you would like to set. The order of the Excel worksheets will align with the order you click the output steps.
User-added image

For both options above, once the sheets are appended in a pre-defined order to the Excel file, changing the steps in the flow or re-running the flow will not change the order of the worksheets. 

Please note, when adding a new output step into the flow to append a new data set, the corresponding new worksheet will be added to the end of the output Excel file.
Did this article resolve the issue?