Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music

Output to multiple sheets within an Excel file

Alteryx
Alteryx
Created

One option to output a file to multiple sheets or tabs within an Excel file is using the Output tool.

 

2018-11-14_12-17-00.png


In this example, we want to create multiple sheets or tabs.  We’ll have one tab per city so that all of the customers in the same city will be in the same sheet or tab.



Configure the Output tool to Change File/Table Name, and select the field to split your file by in the Field Containing File Name or Part of File Name drop-down. For this exercise, the field should be City16.

2018-11-14_12-10-54.png

The output shows a sheet or tab per city.

2018-11-14_12-15-49.png

 

UPDATE 12/8/2015:

 

On a similar note if you would like to have named sheets using the Alteryx Render tool so that you can have nicely formatted excel output with colors and other Alteryx report functionality in the excel output you can use "group by" functionality in the reporting tools along with vertical with section breaks in the final layout tool. See attached example (SheetNames.yxmd) created in 10.0.

 

How do I output to an Excel template file?

It is possible to output your data to an existing Excel document that already has modified formats and column names. For example, the below Excel file has existing data in the first 4 rows. If you wanted to add addresses to this file while keeping the first 4 rows, the first step would be to highlight the area you want to write to. If you don’t know the exact length/width of your data, I would recommend going large:

 


Once you have your desired area highlighted, right-click and choose the Define Name… option:

 



A popup box will appear, enter in a name of your choosing and click OK:

 


You also need to make sure that the sheet you are saving to doesn’t contain any spaces in the sheet name. Once verified, save the template and close out:

 


Below is an example of the sample data that will be added to the above template:

 



In Alteryx, use a Input tool to point to the data you would like to use to update the template file:

 



In the Output, you will want to choose the template file, which will cause the below message to appear, choose yes to overwrite:

 



When saving to Excel, the below window will popup, enter the name you used for the range you highlighted in the template file:

 


After clicking OK, the Output configuration area will populate. Change the Output Options to Delete Data & Append:

 


You can now run the module. Once the module is finished, you can open the updated template file, you should see your previously formatted rows/columns plus the new data you wanted to append:

 



If you set a format to the range you named (color, text style, bold, etc), Excel will keep it so that the data you are writing to the file will appear with the specified format.

Alteryx has a great many ways to output data, not the least of which is the spreadsheet giant Excel. Almost everyone in an analytics space has dealt with data in Excel in some way shape or form in their career, and many use it as their go to format to deal with data or present reports. There are many different ways to output your data to Excel in Alteryx such as:

 

  1. Outputting to multiple tabs with identical schemas in a single output file using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-multiple-sheets-within-an-Excel-fil...)
  2. Outputting to multiple tabs with differing schemas to a single output file Using the Output Tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Multiple-Excel-Files-to-One-Excel-File-with-M...)
  3. Outputting completely separate Excel Files with identical schemas using the output tool (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-to-separate-Excel-files/ta-p/1247)
  4. Output different tables to multiple tabs in Excel (via the Render Tool) (http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-different-files-to-multiple-tabs-in-an...)

 

This article will explore how to output tables of differing schemas in their own tabs in multiple Excel file via the Render Tool. This addresses the specific use case where you have data in two separate tables that are very different in structure. However, in this example we’ll assume that the data in those tables is categorized in to different regions (North, South, and West). The end result is that you want to have 3 Excel files (one for each region), and you want to have two tabs specifically named in each of those excel files (one tab for each table of a unique structure that you want to output).

 

The first step is to complete all of your data processing, once we move to the Reporting Tool set you will lose much of the flexibility to manipulate your data once your tables are converted to “Report Snippets.”

 

To create a Report Snippet for each of your tables, drag down a Table Tool (located in the Reporting Tool set) on to the canvas. Assuming you have data from each region in all of your tables, you will want to be sure to select the “Group By” function in the Table tool. Here you will select the field that contains the Region information. This will tell the Table Tool to output completely separate tables (one for each region). These separate tables will be the basis of creating unique .xlsx files.

 

Following the Table Tool, you can add in a Formula tool and create a new field that can be used to name the individual tabs you are sending your data to in the desired Excel file. This is a best practice when using this method to create multiple tabs and will help the end user intuitively know what tabs contain what data.

 

Now you can use a Union tool to bring everything together. At this point you should have a field you chose to group by in the table tool (in the case of the attached example it will show up as FileName), the Sheet name that you created in the Formula tool, and a Table field which contains the report snippet for each unique table you have in your data.

 

The last two steps are to configure a Layout tool and then finally the Render Tool. In the Layout Tool, select the Orientation option of “Vertical with Section Breaks.” This will tell Excel that you want the data to output to separate tabs. Now you can click on the “Table” entry at the bottom of the tool and mark the Section Name check box, and select the field Sheetname that we created earlier in the Formula tool.

 

 

 

This will tell Alteryx how to name each of the tabs inside each of the unique Excel files you are creating. The final step is to configure the Render tool. In the previous step I have told Alteryx I want to have multiple tabs and that they should be named based on the field “Sheetname.” In this step we are going to tell Alteryx that we have multiple files we want to output, and they will be named according to the field “FileName”

 

 

 

Choose the output mode for “Choose a Specific Output File” and then designate a place for that file to go. Then check the Group Data in to Separate Reports and choose the FileName field. Here you can make some selections regarding how you would like your files to be named. You can choose to append the Group name (in our case “FileName”) to the test.xlsx to create testNorth.xlsx, or you could prepend the FileName or you could even replace test.xlsx altogether with the FileName field.

 

Make your selection and choose your Data Field to be Layout with no spacing between records.

 

Note: Excel outputs from the Render tool will try and accommodate the size of the sheet of paper the Render is trying to write to. Therefore, if you have particularly wide tables, it is best to set the Orientation to Landscape, and alter your paper size to accommodate your large tables. Otherwise fields or field names may get truncated.

 

The attached workflow was built in Alteryx version 10.5 but was adjusted to work in version 10 and above.

Attachments
Comments
7 - Meteor

Great Thanks!

Concerning the layout for multiple sheets, how it works?
If I want to put the "$" or % on my values for multiple sheets after having made calculations.

Alteryx Alumni (Retired)

Riad - 

 

Speaking to this particular example - you would want to create a new field concatenating the CITY16 field with "$" or "%" and have the output tool reference that new field (probably unchecking the include in output option). 

 

-Cameron

9 - Comet

This works for me when I'm writing a new Excel file or overwriting sheets on a basic Excel file, but if I'm writing the same data to a target file that has been created by the Reports-->Render tool, I get an XML error. The sheets are created, but not populated.

 

Any ideas?

 

Excel_Error.PNG

Error_XML.PNG

Alteryx
Alteryx

Hi Daniel,

 

Thanks for this report, I will log a defect and take a look at this as soon as I can.

 

If you have a small workflow that replicates this error, it would be helpful for me.

I want to make sure I test with the same configuration of tools you're using when you see this problem.

5 - Atom

What about exporting to different tabs of the same spreadsheet for different paths in the workflow?  Specifically, I'm trying to take your "tile" example and export each resultset to a separate tab in a spreadsheet so I can see them side-by-side in a familiar format.  I couldn't link all 6 tiles to the same output file, which I was disappointed by but I guess makes sense.  (I was hoping it would be able to take each individual input path and create a separate tab for each one.)

 

So I set one up and copied/pasted it and connected the others, each to an individual output tile, all pointing to the same xlsx file, each saying to create a new sheet.

 

But for each one I get a message "Must specify new sheet name".  In the output itself I don't see a place to specify the name.

 

Is what I'm attempting feasible?  If so, how do you suggest I go about it?

Alteryx
Alteryx

Hi Bob,

 

The post here may help: http://community.alteryx.com/t5/Data-Preparation-Blending/Re-Output-to-multiple-sheets-within-an-Exc...

 

In your case the setting must be on "Create New Sheet" but the Sheet Name has not changed between outputs. The Sheet name is after the Pipe (|) in the filename:

 

Image 001 - 20160212 - 120253.png

 

 

5 - Atom

Thanks!  This and the related thread I think are just what I needed to get me to the next step.

5 - Atom

I have been tasked with testing / learning Alteryx for our company and have been going through the self-paced training to get familiar with the tools.  When trying to output the customers.csv file to multiple tabs in the worksheet I have only been able to get it mostly work.  Using the formula tool I created a column called SheetName which concatenate the city & state.  This creates 20 unique values so I should end up with an Excel spreadsheet with 20 tabs.  After configuring the output tool as defined above, the workflow starts to take the records & create tabs based on the City, State.  

 

Filename:  \Customers by City, State.xlsx|||Sheet1

SheetName Settings.PNG

 

The output tool successfully created an Excel worksheet with 3 tabs but this threw the following error:

Error: Output Data (17): Unable to open file for write: C:\Users\matthew.mossman\Documents\Temp\Customers by City, State.xlsx Error Opening file: C:\Users\matthew.mossman\Documents\Temp\Customers by City, State.xlsx
: The process cannot access the file because it is being used by another process.
(32)

 

How do I get rid of this error so that the entire workflow can complete?

Alteryx Certified Partner

@matthewmossman

 

Not sure if you already found the answer, but you can use the function "Block Until Done" under the Developer section. This ensures that the workflow completes a process (and in this case closes the excel file and enables the next output function to access the file).

 

I found a short tutorial here: https://www.youtube.com/watch?v=a6WH0uYZ3TE

 

Hope this helps 🙂

 

Edit: This was actually what KaneG linked to in a previous post, sorry for double posting 🙂

5 - Atom

Good thread here, thanks.

 

Relative to the Block Until Done tool . . . I have a workflow that outputs six tabs to two excel files each for a total of twelve writes and pretty quickly ran into the same error described above.

 

I found the Block Until Done tool and tried it and have the same error much less often, but it still happens occasionally, using version 11.0.5.26351. Just re-running the workflow seems to work when this happens, but, really?

 

In addition to this particular tool with no configuration not being 100%, it also seems that version 11 is a bit buggier than previous releases. A victim of success?

 

With the same workflow, I am Alt>Tab'bing back and forth between Alteryx and Excel checking various things and find that Alteryx likes to take over the focus regularly. I have to click over to Excel twice for Alteryx to figure out that I really want to look at what is going on in Excel. This is rather annoying.

 

Just more stuff to add to the bug list, thanks again.

11 - Bolide

Thanks for that solution I've been trying to do this with block until done but it doesn't work in my flow very well.

Alteryx Certified Partner
Just created a quick how-to 'output to multiple sheets' video on YouTube. Hopefully this is useful for people that are new to this concept. https://www.youtube.com/watch?v=GKeSTZhuEQ8
5 - Atom

Just created a quick how-to 'output to multiple sheets' video on YouTube. Hopefully this is useful for people that are new to this concept.


Andre, thanks for that. It's a good video on the basic approach, outlining what was included in the original question and answer in this thread. But it doesn't seem to deal with the more recent questions and issues dealing with file lock contentions and the like...

Alteryx Certified Partner
Yes, I know. I didn't look at this thread before I created the video. If I find some time I'll adjust the video. Thanks
8 - Asteroid

Hello,

 

I use layout with vertical with section breaks as orientation.

 

I have to format each sheet separately. I have two sheet: The first with 2 column and the second sheet with 30 column. Cells size of my sheet 1 is to wide 

 

Please can someone help me how to achieve this?

 

 

 

7 - Meteor

I am able to set up Alteryx to write data to different Tabs alright, however I cannot seem to control the order in which they are written. Does anyone know if it is possible to use an order other than Alphabetical for the tabs in an XLXS file?

5 - Atom

Andrew, it's not ideal, but you could add an integer at the front of the tab name, such as 1-Dogs, 2-Cats.  Just be sure to use leading zeros of you have more than a single digit, because it will sort alphabetically (1, 10, 11, 2, 3, 4, etc.)  Hopefully there's a better answer than this for you.

8 - Asteroid

@AndrewK 

 

If your tabs arent too dynamic you could create a table with the tab name and a column for sort order - first tab = A etc. Then pass the records to a batch macro with your output file. Should sort the issueMacro.PNG

Workflow.PNG

9 - Comet

@AndrewK Another option is to use the Parallel Block Until Done macro to force a specific order of data output tasks. Set them up to run in the order you want, and the sheets will follow. Doesn't do much good for dynamic sheets with varying sets of tabs, but for a repeat report etc., it works fine.

8 - Asteroid

Hello, I also have this problem where I need the output in different tabs. Unfortunately, my data is in one line and I can't specify a Sheet Name for each tab but would like the output for multiple files to be output to different tab in one excel file, with Sheet1, Sheet2, etc.

 

Any idea how this can be done?

9 - Comet
Maybe see what happens if you make a custom 'Group' field that uses the
pipe delimeter. Not sure it'll work, but something like
[FileName]|[SheetName]? Then pass that to the output tool and cross your
fingers.
--





*Daniel Taylor* | MMi | Manager, Data Services and Analytics
Main: 312.754.4130 |
*Direct: 312.754.4123 *901 W. Jackson | Suite 500 | Chicago, IL 60607