community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.

Output to multiple sheets within an Excel file

Alteryx_KB
Import
Created on

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.

Comments

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
Alteryx

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

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.

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

 

 

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

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 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 Smiley Happy

 

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

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.

Comet

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

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
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?

 

 

 

Atom

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?

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.