on 05-08-2013 07:50 AM - edited on 07-27-2021 11:49 PM by APIUserOpsDM
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.
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.
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.
"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.
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.
The Output tool will write separate Excel files, the name and path of the files are the ones created by the formula.
Thanks, exactly what I needed.
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.
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!
Awesome solution! Thank you very much.
So helpful & exactly what I was looking for. Thank you!
@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.
@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.
Thank you! Very helpful!
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]
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.
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 ?
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.
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?
I just tried that too and no luck...I keep getting a specify sheet name error. I don't understand why.
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.
Hi,
I am getting an error saying you must specify a sheet name while trying to output multiple excel files. I have specified the sheet name in formula tool but still getting the same error. Can someone help me with this please.
@Shilpanaveen can you please share the Formula that you had given.
Usually after the filename you should use the 3 Pipe symbol (example: test.xlsx|||Sheet1) then it should work.
Another option if you don't mind the file type is to use csv
great thanks to @Arch for answering the questions I had when reviewing this solution!