We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to output separate excel sheet for each data stream

Ko
6 - Meteoroid
Created

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.pngimage.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.pngimage.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.pngimage.png
  5. Using Select Tool and Data Cleansing Tool , remove unnecessary columns for each output.image.pngimage.png
  6. Add Output Tool and set to write to Sheet 1 on an xlsx file. Please select the desired Output Options accordingly.image.pngimage.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.pngimage.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.pngimage.png
  10. Add the batch macro and set [stream] field for the first anchor: Control Parameterimage.pngimage.png
  11. Set [stream] field for the second anchor: Control GroupBy, Input1 GroupByimage.pngimage.png
  12. Run the workflow
 

Note

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




 

Additional Resources

 
Attachments