How to output separate excel sheet for each data stream

[Ko]

How to output a separate excel sheet for each data stream

This is one of the ideas of using a batch macro to output a separate excel sheet per data stream within one workflow.
 

Prerequisites

 

• Alteryx Designer
  • Version(s) : 2021.3
• Microsoft Excel
  • 2202

 

Procedure

Assume that there are three data streams in a workflow and the user wants to write them to a separate sheet in an excel file.

image.png

1. Using Formula Tool, add a stream number field to each stream data.
2. Using Union Tool, combine datasets to pass to a batch macro.image.png
3. At this point, open a new blank workflow to create a Batch Macro.
4. Add Macro Input Tool and add a sample data set.image.png
5. Using Select Tool and Data Cleansing Tool , remove unnecessary columns for each output.image.png
6. Add Output Tool and set to write to Sheet 1 on an xlsx file. Please select the desired Output Options accordingly.image.png
7. Add Control Parameter Tool and Action Tool. Set up the Action Tool to replace "Sheet1" with the value passed from the Control Parameter Tool by using the "Update Value" action type.image.png
8. Save the macro and back to the original workflow to use the created macro.
9. Add Summarize Tool in order to eliminate duplicate [stream] value, which will be used for the sheet name by passing it to the first anchor of the batch macro.image.png
10. Add the batch macro and set [stream] field for the first anchor: Control Parameterimage.png
11. Set [stream] field for the second anchor: Control GroupBy, Input1 GroupByimage.png
12. Run the workflow

 

Note

Attached is a sample workflow for demonstration purposes, and not for use in a production environment.




 

Additional Resources

 

• Output multiple excel files with multiple sheets based on 2 different fields
• How To: Import Multiple Excel Sheets or a Specific Excel Range into a Workflow