This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 05-08-201307:50 AM - edited on 12-30-201908:52 AM by HimeshP
One option to output a file to multiple sheets or tabs within an Excel file is using the Output tool.
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.
The output shows a sheet or tab per city.
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:
This article will explore how to output tables of differing schemas in their own tabs in multiple Excel file via theRender 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 aTable 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 aFormula tooland 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 aUnion toolto 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 aLayout tooland 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.