Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Output to separate Excel files

Alteryx
Alteryx
Created

If you have a file that you want to output to separate Excel files you can first create the desired file path with the Formula tool and then utilize the Output tool to change the entire path.

1247 1.png

This example illustrates a file with records from different states, where we wish to create a separate file for the records belonging to each state.




1247 2.png

With the Formula tool, create a string field that will contain the file path, where the path includes the field you’ll use as a part of the file name.  Here we are using the State_Name field. “C: emp” is the path where your files will be saved to.


1247 3.png

"C: emp"+[State_Name]+".xls|sheet1"

The field you are creating, and the field you are including as the variable path name (State_Name) have to be of a string type.  If your variable path name field is not a string type then you can convert it with the TOSTRING function.

1247 4.png

In the Output tool, select Change Entire File Path and select the newly created field. Uncheck Keep Field in Output so it won’t add as a field the path that was created with the Formula tool.

1247 5.png


The Output tool will write separate Excel files, the name and path of the files are the ones created by the formula.

Attachments
Comments
6 - Meteoroid

Thanks, exactly what I needed.

5 - Atom

Alteryx_KB. This is very helpful.

 

Is there a way to output all the separate excel files into a folder where the user can choose the folder before having the files written to? Instead of defining the output file path, I'd like to have the user choose the folder.

7 - Meteor

This is very helpful. I was exactly looking for this. I was trying to build a batch macro for this but now I found that there is a better way to do this. Thanks!

5 - Atom

Awesome solution! Thank you very much.

5 - Atom

So helpful & exactly what I was looking for. Thank you!

7 - Meteor

@Alteryx_KB Thanks for sharing the workflow in community and this helps me to blend and separate my data into different files.

 

But I also want to create the different tab within each sheet.

 

For example in the above based on the above by using the formula we have created the file name and in output we have given file as out put so it creates the different files how ever in the Formula if we want different sheet within the each output excel can we give the Name as tab name in each State.

 

Please help me what is the best way get the same.

 

5 - Atom

@Alteryx_KB

 

Thanks for the sample workflow. However, when I try to run the workflow based on the above idea i get an error stating 

"Error while appending to existing sheet in Excel. File does not exist:

 

Is there anything else that i am missing.

 

My objective is to create separate excel files the first time i run the workflow and then append the data to the existing excel files when i subsequently run the workflow.

 

Thank you in advance.

 

5 - Atom

Thank you! Very helpful!

7 - Meteor

In response to maha7060, if I understand the question to be you want to change the sheet name to be the state name in each file you could change the formula tool to be:

 

".\" + [State_Name] + ".xlsx|" + [State_Name]

 

If you wanted to put all the states in one file, but have each tab be a different state, then you would change the formula to:

 

".\AllStates.xlsx|||" + [State_Name]

 

 

7 - Meteor

In response to mohitmundada, you need to change the path in the formula tool from "c:\temp" to a path that works on your computer, or just use ".\" to have the files created in the folder where the workflow is located.

5 - Atom

How do I need to modify the formula if I want to have multiple excel files named after the states and within each file I want to have a multiple sheets, one for each state ?

6 - Meteoroid

I tried this approach and keep getting a "specify sheet name" error. Is there some error I am making with the formula?

 

"C:\Users\e0001\anv\Team\Report Delivery Folder\" + [Client Region]+"PY Hours by Month.xlsx/sheet1"

On the output tool I have the following selections:

Write to File or Database: Routing to the folder where I want the Region Excel files to be

Output Option: Overwrite Sheet or Range

Check marked Take File/Table Name From Field

Field Containing File Name: Client Region is selected

Keep Field in Output check marked

 

 

The goal of my output is to use the Client Region field and output a different excel file for each region from this file.

Alteryx
Alteryx

@kthakk7 

The screen shot in the article shows the text in italics.  The Excel file name is separated from the sheet name by one or three pipe characters | or |||.  It looks from what you've written above that you're using a forward slash to separate them.  (PY Hours by Month.xlsx/sheet1)  That won't work.  Does your comment above contain a typo?

6 - Meteoroid

I just tried that too and no luck...I keep getting a specify sheetOutput Error.PNG name error. I don't understand why.

7 - Meteor

What do you have in the Formula Tool for the Client Region? That is what is important, as you are taking the entire file path from that field. Make sure that formula is using pipes to separate the sheet name from the workbook name.