Alteryx Designer Desktop Knowledge Base

Ever have to output tables of differeing schemas to the same Excel workbook? Ever need to output to different tabs? This article covers your bases with the cunning use of Reporting tools! Also included are links to other helpful "outputting to Excel" Knowledgebase Articles.

Have you ever tried writing to multiple tabs within the same workflow and have received an error? This article is for you!

How to save your predictive model.

When creating reports within Alteryx, you can name Excel tabs by specifying a column to use in the Layout tool. This is done in a 3 step process. Step One:   Choose your grouping column; below, the Table tool is being used and [DMA_Name] is being used for the Group By field. This would also apply with the Charting and Map tools.         Step Two:   In the Layout tool, change the Layout Mode to Each Group Of Records. Next, choose the column you would like to Group By; for the example we will use [DMA_Name]. Next, change the Orientation to Vertical with Section Breaks. By doing this, the Section Name option will appear at the bottom of the tool configuration and allow you to choose the column you would like to use to name the Excel tabs.       Step Three:   Update the Render tool to create an Excel spreadsheet. In our example, each of the Excel tabs was named for the DMAs contained in the data.           Keep on reporting!

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.

If you have two or more files, different structure, and you would like to output each file into a separate tabs in an Excel spreadsheet. You could use the table tool to create snippets and the Layout tool to create sections breaks. Bring in your files using the Input tool and connect them to Table tools to create the snippets.  Finally, Join them by record position. The Layout Tool properties should look as follows.  Select Vertical with Section Breaks for the Orientation setting. The output will show each file in a separate tab:

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.

Tips and tricks on how to output multiple sheets to an Excel file with the Output tool or with Reporting tools. 

A user posted on the Forum that the ampersand character (&) was causing an error when importing a series of KML files. The fix? Have a prior Alteryx process replace this character with the word 'and' within all KML files without ruining the rest of the file. To accomplish: Pull in an Input Tool and set the file type to .csv with no delimiter (designation is \0). In the Input Data Source portion, use the wildcard (*) to pull in all of your text files (using KML in the example). For the option Output File Name as Field, select File Name Only. Use a Formula Tool to update the text within the files. Output each file individually with one Output Tool, making sure to set the output to dynamically update based on your file name field. The process is fairly straightforward and should help resolve any text or character issues that may be an issue. For an example, please see the attached workflow.

  One of the great things about Alteryx is the ability to have multiple geography types (points, lines, regions) all contained within the same record.     However, when exporting map layers to MapInfo Tab file format, a decision has to be made of which spatial field to keep.  What if you want all of them?!     Not to worry! The Transpose tool will save the day!     To configure the Transpose tool all fields will need to be selected under the Key Fields except for the spatial fields, they will be selected under the Data Fields.       Just note that there are now 2 fields for the radius. RadiusSize will correspond to the first trade area (row 2) and RadiusSize2 will correspond to the second trade area (row 3).   Now, more importantly all the spatial objects exist in one field (the Value field) and can now easily be exported to MapInfo.      Note: this methodology will also work for ESRI shapefiles, however, all the spatial objects must be of the same type. All points, all polygons...   (An example workflow is attached. This workflow was created in Alteryx 10.0)

Is it possible to append to a YXDB instead of having to read in the whole thing, union the new records, and then re-create it? The short answer is, No, but you can accomplish this with SQLITE.

how to reference other cells when creating formulas in excel

How to use Runtime setting in the Workflow Configuration options to disable output and Browse tools\n  

The partnership between Alteryx and Tableau is becoming stronger and stronger, and the seamless effortless integration has been made easier through the Publish to Tableau Server Tool. This article demonstrates the use of the Publish to Tableau Server tool, available on the Alteryx Analytics Gallery.

By combining Alteryx and Microsoft Power BI, organizations can streamline and accelerate the process of preparing and analyzing data. This provides a faster way to deliver an end-to-end experience for data access, preparation, analysis, visualization and consumption — delivering deeper business insight faster with a more complete set of data.

Having trouble reading or writing Microsoft Access files? No worries - Client Services is here to help!

This article will guide you through the steps of as an example of uploading a file to FTP

You may have a use case whereby you have a large dataset and you want to output it to separate excel files. However, in each of these excel files you would like to apply a template format.

In a previous article, we've shown you how you can upload to FTP using the Download tool. With the release of Alteryx 10.5, the Download tool now supports uploading to SFTP. With this addition, we'll take the opportunity to show you some more examples of uploading data to SFTP/FTP and show you how seamless it can be.

Did you know that instead of having to choose a save location in the Output Tool, you could leverage the directory that is used to save your temporary files?

The Message Tool enables users to write custom messages to the output log