Alteryx Designer Knowledge Base

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.

How to check for encoding or formatting issues with Excel worksheets   When Excel worksheets are used in Alteryx Designer, sometimes Designer has difficulty reading the data. Viewing the worksheets in XML format is a good way to check for encoding or formatting issues as all of the encoding and formatting is shown.   Prerequisites Product - Alteryx Designer, Gallery, or Server and an Excel file used in Alteryx workflows or applications   Procedure To view in XML, open the Excel worksheets in 7-zip or another application used for .zip files. Use the format C:\folder_path_of_the_workbook\Excel_file_name.xlsx\xl\worksheets. Next, click on the sheet name and the XML for the worksheet is shown.          Notice the type of encoding is listed at the top. Followed by the formatting schemas, and then formatting for the worksheet. If needed you can change the extension of the worksheet from .xml to .txt and edit the encoding or formatting of a worksheet to match a working example, and then change the extension back to .xml.    To edit, right click on one of the sheets, select Rename, and change the file extension to .txt. Edit as a text file, and then Rename again to change the extension back to .xml.                                    Common Issues  If the Excel workbook is created automatically, such as being created by a script, information needed for Designer to open the worksheets may be missing. Excel can sometimes add this information when opening the worksheet, but Designer is not able to add default encoding and formatting if it is missing. If you come across an issue, it is a good idea to try adding example data directly in Excel and saving as Excel should automatically add the needed encoding and formatting. Afterward, you can compare this with the broken file, and update it to match the working example.    There are other possible reasons why Designer may not be able to open an Excel spreadsheet. However, checking for encoding and formatting issues in XML view will catch many of the problems with opening Excel data in Designer, so it is a good place to start.    Additional Resources Character encoding Microsoft Office XML formats Structure of a spreadsheet  

Issue    During run time of workflow, the following error is received:    Error: Input Data (1): Error SQLExecute: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 99, HTTP Response Code: 1759505392, Error Message: Unable to connect to endpoint [Execution ID: e7fe279d-f39b-4872-b37d-8ad49d49f3f5]   Environment   Alteryx Designer Amazon Athena Environment Windows Operating System Amazon Athena ODBC Driver (found here)   Cause   In version 1.0.3 and greater of the ODBC Driver, Result Set Streaming is enabled by default which has extra requirements. More info on the requirements here.   Solution - Connection String (DSN-less) If connecting via a Connection String: Add UseResultsetStreaming=0 to your connection string Example string:  odbc:Driver={Simba Athena ODBC Driver};AwsRegion=[Region];S3OutputLocation=[S3Path];AuthenticationType=IAM Profile;AWSProfile=[YourProfileName];UseResultsetStreaming=0 Use this string with your standard tools (Input Data, Output Data) or with a Connect In-DB Tool. Solution B - DSN Connection  If you are connecting to your Amazon Athena environment with a DSN connection (via ODBC Admin): Open up ODBC Data Source Administrator  Find your Athena connection and select it Hit "Configure..." Hit "Advanced Options..." Uncheck the "Use Resultset Streaming" box Hit OK to save the Advanced Options Hit OK again to save your connection  

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

Output data as Text File   Within Alteryx there isn't an output option directly to a text file. To achieve this you will need to use a flat ASCII file.   Step 1: Bring in an output data tool and choose the ‘Flat ASCII file (*.flat) option   Step 2: You will then see this below screenshot in the output tool configuration window.     Step 3: You will now need to change the file extension from .flat to .txt, this will chage the flat file to a text format.       Step 4: You can now click on the hyperlink in the results window and open your text file in a supported application.         Example attached.

Connecting to Hadoop HDFS/Hive/Impala/Spark with Alteryx Designer.

  We get quite a few requests asking how to add the current date to Excel spreadsheet file names. The difference in adding information to your file name in Excel and other output configurations is that you have to use the Reporting tools for Excel to accept the new file name.   Why would I have to use the Reporting tools, you ask? Simply put, Excel thinks that when you are adding a prefix or suffix to the file name that you are stating that additional information is the “Sheet Name” and not appending the file name from the Output Tool. The Render tool in the Reporting tab on your palette basically tricks Excel into thinking that you are creating a separate report and is able to append the date.   In order to add the date to the file name you will connect these tools to your workflow in the order, you see below.      In the Formula Tool, create a new field that is a String type.  Go to the DateTime functions and add the DateTimeToday() expression. No configuration of the expression is necessary.      The Table tool will allow you to Group By this new field. In the GroupBy Configuration, choose the Date field you created in the Formula. In the Per Column Configuration, uncheck the new field. This will allow you to group by the new field name in the Render Tool and still remove it from the data so that your new field does not appear in your report.          Use the Render Tool to output your Excel spreadsheet with today’s date in the file name.   Output Mode - Choose a Specific Output File. Output File - Specify the Excel 2007 Spreadsheet (xlsx) and point to where you would like to save the documents. The file name you specify will be replaced with the date in step 5 below. You will then check the box that says Group Data into Separate Reports. Field to Group on - You will choose your new field that you created. Modify Filename By – Replacing Filename With Group. Report Data – You will choose Table and can leave the rest as defaults.  

  When writing out plain text files you have a couple of different options, a popular one being .csv files. CSV stands for comma separated value, which indicates that a comma is used to separate values within the file. The default program for opening .csv files varies but it is Excel on most machines. When opened in Excel, a .csv file will look just like an Excel document with the only difference that all fields are text (strings). When opened in a text editor, you can see the comma separated values:   However, you don’t have to use a comma as a delimiter; Alteryx will let you choose many delimiters including pipe |, tab \t and even no delimiter at all (Indicated by \0 ). You also don’t have to use .csv as the file extension.   In this example, even though the file format selected is csv, the file actually has a .txt extension and will be delimited by pipes | :       Other options when writing out delimited files are:   Max Records Per File – you can limit the number of rows to write out.   First Row Contains Field Names – uncheck if the first row is not to contain field names and Alteryx will ignore the field names.      Quote Output Fields – the options are auto, always, and never. Quotes are used in delimited files to indicate whether a delimiter is to be treated as such or as part of a field. For example, if the address 123 Main St, Apt 2 is quoted and the delimiter is a comma, it will be treated as one field. If the address is not quoted, it will be treated as two columns (This can sometimes lead to problems when reading in delimited files with Alteryx).   Code Page – You can select the code page for your document. The default for CSV is Latin I which completely covers all characters in the English language. Unicode is also available.   Line Ending Style – You can select the line ending style to be used for your delimited files. Available options are Windows (default), Unix, or Mac.

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!

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.

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 use Runtime setting in the Workflow Configuration options to disable output and Browse tools  

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.

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

When writing to Excel, have you ever had the need to populate a column with values that come from formulas referencing other cells in your data? You’ve probably noticed that when writing to either .xlsx or .xls Excel file formats that your output still appears as a string, rather than a calculated value, like the below:   .xlsx Each formula cell has the proper cell references and syntax but will need to be interacted with (add or remove a space in the formula and hit enter on your keyboard) to calculate the true formula value.   .xls When expanding on the string you’ll see there’s an apostrophe (‘) preceding the formula and you’ll need to remove each and hit enter on your keyboard for the formula to execute.     In the attached v10.1 workflow, Writing to Excel with Formulas.yxmd, we provide a sample formula expression to build formulas with basic cell indexing (using a record ID field) and the ability to write formulas that can be interpreted by Excel. This is done by leveraging the .csv file format; when excel opens this output file type it automatically recognizes the formula syntax and will interpret it as such – populating each cell with the true formula calculated value:  

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.

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:

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.

Multiple files can easily be combined into a single Excel file containing multiple worksheets (or tabs).  The format of the files do not need to be the same; they can be completely different.    In the example attached to this article, two files, a Customer .csv file and a Store .xlsx file, are output to a single Excel spreadsheet (.xlsx format) with one tab for Customer and another for Store. These files contain different data elements and have different layouts.    The first step is to create a new field in each dataset (e.g.: 'TabName') and set the expression to the name you want each worksheet to have. In the attached example, 'TabName' is set to 'Customer' and 'Store,' respectively for each dataset.    When you get ready to output your data, use an output tool for each dataset and configure them in the same way.  The file format is 'Microsoft Excel (*.xlsx)' and when prompted to select a worksheet for the output, select 'Sheet1'. Check the 'Take File/Table Name' checkbox located at the bottom of the configuration window. Select 'Change File/Table Name' in the dropdown and under 'Field Containing Name or Part of File Name' enter 'TabName'.  You probably don't want TabName in your final output, so uncheck the 'Keep Field in Output' checkbox.   

 When attempting to publish a workflow to your private Gallery which contains the Publish to Tableau Server macro, you may encounter an error similar to this:   “ Cannot find macro "PublishToTableauServer_v1.08.1\Publish to Tableau Server.yxmc"   This is typically due to one of two reasons:   The Publish to Tableau Server macro has not been installed on the Alteryx Server where the Gallery is located. The macro has been installed but has not been installed in the correct location, or the Gallery cannot access the macro for other reasons. Please see this link for instructions on correctly installing the Publish to Tableau Server macro on an Alteryx Server.   Even when the macro has been installed properly on the server, this issue can still occur, and if it does, here is a simple workaround to get your workflow published:   Save the workflow to the Gallery: From Designer, click on File > Save As and choose your Gallery. Add the Tableau macro components as assets of your workflow: Click on Workflow Options > Manage workflow assets. Select all of Publish to Tableau Server related assets (currently there are four). Including these assets when publishing should allow the workflow to publish and run successfully in the Gallery.   Thank you!