Alteryx Designer Knowledge Base

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!

As users of Alteryx become increasingly international, it is important to support the need to prepare, blend and analyze data in a large number of languages. However, many users with data in non-English languages encounter roadblocks at the first step of their data analysis: inputting the data into Alteryx.    This article will demonstrate two ways of bringing double-byte characters (DBCs) into Alteryx.  These particular types of characters are associated with languages that have many unique characters or symbols, such as Chinese, Japanese and Korean (CJK).  These languages’ graphic characters are stored in two bytes of data rather than just one, which is sufficient for languages like English, French and Spanish (among many others) that can be represented by 256 characters or less.  Chinese, Japanese and Korean languages require a fixed width sequence of two bytes for every character, which allows for about 65,000 characters.  This need to support such an extensive dataset requires an Alteryx user whose data may be represented by CJK characters to consider certain formatting and preparation steps both before and after inputting data into Alteryx.    Pre-Input Considerations Before inputting data into Alteryx, data with CJK characters requires certain encoding and file type considerations.  Encoding is the process of converting data from one format into another specific equivalent code that uses letters, symbols and numbers for storage and processing.  When using CJK characters, encoding your data in a format that supports double-byte characters is important to accommodate the range of characters in your dataset.  Storing the data as Unicode ensures that you do not lose data as you begin your inputting process into Alteryx (Figure 1).   Figure 1: To encode your data from Excel, click “Tools> Web Options” in the bottom right-hand corner of the Save As Window.  Then, select “Encoding” from the file tabs. In the Dropdown menu to Save the Document, select “Unicode”, then click “OK”.   Make sure that your file name does contain any non-English characters, as that can cause issues the Input tool.    The second pre-input consideration for CJK data is the file extension, or type, with respect to how the data is saved.  For example, saving CJK data directly to a Comma Separated Values (CSV) format, especially without encoding, is discouraged.  This is because CSV file formats distort characters other than those supported by the American Standard Code for Information Exchange (ASCII) text.  In the case of CJK characters, that is likely to be all of your data (Figure 2)!  Rather than suffer through the heart-wrenching experience of seeing all your data turned into meaningless question marks, try saving your data as a Unicode text file (.txt) or Excel workbook (.xlsx), both of which are supported by the Alteryx Input Tool (Figure 3).  I have found that using CJK characters in these file formats ease the process of inputting data into Alteryx and even reduce some of the steps you may need to perform in your post-input process.     Figure 2: Rats! CJK characters that are saved directly to CSV format without encoding turn into meaningless question marks.  WHHHYYYYYY????? Figure 3: Rather than lose your precious data, save as Unicode Text or an Excel Workbook (encoded as Unicode as a safeguard).   Post- Input Considerations Because CJK characters are double-byte in size, it is important that their field type is set appropriately to display the data completely.  Generally, importing data stored as Unicode text or a Unicode-encoded Excel workbook will be read in as a variable length wide string format (V_WString) to accommodate these wider types of characters (Figure 4).  As you can see, forcing the field type to a narrow string format (V_String) leads to data loss.  Throughout the data preparation and blending process in Alteryx, you should be sure that fields containing CJK characters have the necessary space requirements to store and transmit the data.  The sudden conversion of CJK characters to question marks may indicate that your field type has changed; should this occur, field types can be easily changed using a Select Tool.   Figure 4: The larger size requirements of CJK characters creates the need for using wide string fields (V_WString).   Datasets that contain a mix of Western Latin character sets and CJK characters may require the conversion of text between Code Pages.   This may be especially necessary if data has been transferred among many users who have different computer language settings or encoding systems.  Figure 5 shows data that has been transferred from a Chinese colleague to an American Alteryx user.  Despite bringing the data in as Unicode Text, the data is still unreadable.  To convert the data to a useable and meaningful format, Alteryx tools with an Edit Formula Box (or Expression builder), such as the Formula or Multi-Field Formula Tools, contain ConvertFromCodePage and ConvertToCodePage functions.  These functions facilitate the conversions between language codes and Unicode.  In the below example, the Multi-Field Formula tool is used to overcome coding issues to convert data from the original Chinese code to Unicode using the ConvertFromCodePage function.  As a result, the data is displayed in a readable and useable way.   Figure 5: Converting the data from Code Page 20936 (Simplified Chinese GB2312) to Unicode using the ConvertFromCodePage function renders the data readable and useable…Hooray!   And voila! Errr… 这里是! Now your CJK characters are brought into Alteryx beautifully!   Have you had experience with inputting non-English language data with Alteryx?  Post any helpful tips or tricks that you’ve used to the Comments section to share with the Alteryx Community.   **Thanks not only to the Alteryx users whose data prep needs have inspired this article, but also to Alteryx’s RodL for his Community post that has provided me with the steps to even know where to begin when troubleshooting data in a variety of languages including Chinese, Japanese, Korean, Thai and Vietnamese.  

To find the full path and filename of a saved file in Excel, you use the =CELL function.       In Alteryx, you use a Field Info Tool to get this information:     The Field Info Tool allows you to see in tabular form the name of fields in a file as well as the field order, field type, and field size.     Name: field names within the file Type: type of data field Size: length of a data field Scale: with respect to fixed decimal data types, scale refers to the digits of precision Source: contains the full path and filename Description: may or may not contain information; you can add a description via the Select tool   We're only interested in the Source field and this information will be the same for each field.       Using a Sample Tool, we select just the first record:       Notice the data in Source begins with 'File:'.  We don't want that in the final output so we'll use a SUBSTRING function in the expression of a Formula tool to clean it up.  Complete the workflow with a Select tool so we only get the Source field:            I'll mention here you can use the Directory Tool to find the full path and filenames in a directory.   Select the directory you want to search.  File Specification has wildcard characters so you can limit your search to files containing specific character patterns or file types.  In the example below, let's set up the File Specification to only return files with the '.xlsx' file extension:      We're only interested in the field 'FullPath' (first column) so we'll use a Select tool to drop the remaining fields.        While the Directory tool returns multiple filenames, it will not contain a worksheet name if the file is an Excel file.  To get that information, you'll need to use the Field Info Tool as we did above.

Alteryx allows you to connect to many different types of data sources. One type of data source you can connect to is a database. Examples of databases are SQL Server, Oracle, Teradata, and MongoDB; amongst many others. There are several connection methods to connect to database sources including ODBC, OleDB, or natively. This guide will focus on connecting to a database via OleDB. This guide will particularly focus on setting up an OleDB connection to SQL server, however, the same general process can be followed to connect to any database via OleDB. ***If the OleDB driver for your database is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found here. If you are unsure of where to get the driver or which driver to use please contact your IT or database admin for this information.   Open alteryx designer and drag and drop the “Input Data” tool onto the canvas       Click the dropdown to "Connect to a File or Database." Then select "Database Connection" > "New OleDB Connection…" The "Data Link Properties" window should appear     Select the driver for the database you wish to connect to and click “Next.” In this guide we will be selecting the “Microsoft OLE DB for SQL Server” driver     On the next screen, enter the server name by either selecting it from the dropdown, or if it does not appear, by typing it in manually Select the authentication method of the database. If your database uses “Windows Authentication” additional login information is not required. If your database uses user ID/PW authentication please enter the username and password you will be connecting with. If you are unsure of your login information please contact your IT or database admin Select the database on the server you wish to connect to by selecting it in the dropdown Click the “Test Connection” button to test that you are setup correctly   ***Step     Upon successful test you will get the following popup window: Click the “OK” button On the “Data Link Properties” window click “OK”     The “Choose Table…” Window should appear In the window select the table you wish to connect to:     After successfully connecting to a table, the connection will now be saved in the “Connect a File or Database” dropdown, both under the initial dropdown and also under “Database Connection”     Congratulations! You have successfully created and saved and OleDB connection!

As of the release of version 11.0, the Alteryx Designer supports reading in single and multiple input files in a zipped archive file (.zip) .  Now, multiple files of the same file type in a zipped archive can be read into the Designer in a standard Input Data Tool .

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

In this article, we demonstrate how to import multiple worksheets from one Excel file, how to import multiple worksheets from multiple Excel files, and how to import a specific range of cells from an Excel sheet.    How To: Import multiple worksheets from one Excel file   In the case where you have one Excel workbook with several sheets, you can use a Dynamic Input tool to import the sheets instead of an individual Input tool for each sheet.   Note: for this method to work, the sheets of the Excel file need to have the same schema.                  Use an  Input Tool to select Sheet Names. Connect a Dynamic Input tool to the Input tool. Configure the Dynamic Input tool to read a list of Data Sources from the Sheet Names field provided by the Input tool. The workflow is now configured to import the Excel sheets in a single input stream (you can see the Regions South and West from the two different Excel sheets).    How To: Import multiple worksheets from multiple Excel files   When you need to import multiple sheets from different excel files, you can modify the above method to work by turning it into a Batch Macro.   Start with the same workflow from the previous example. Add a Control Parameter tool  and a Macro Output tool to the canvas. Connect the Control Parameter tool to the top of the Dynamic Output and Input tools. You should see two Action tools being automatically added to the canvas between the interface tools and the standard tools. Connect the Macro Output tool to the output stream of the Dynamic Input tool. Your canvas should now look like this: Now, we need to configure the Action Tools. The action type should be set to "Update Value". We need only to update the file name without changing the sheet name. Therefore, for both Action Tools "Replace a specific string" should be enabled. Please note that this string should contain the path to the file to input without the extension for the sheet.  If the Excel files have different schemas, in the interface designer we can set the macro to Auto Configure by Name or Position so that our workflow does not error out.  Note: sheets within the same file will have to be the same schema. By default, the Interface Designer window will be displayed in the left-hand side of the Designer window.  Click on the Cog icon in the left bar to access the Properties tab. Select the Output fields change based on macro's configuration or data input option.  You now created a Macro. In order to use it, you will need to add it to a workflow. Save the Macro as a Designer Macro (*.yxmc). Do not close the window with the Macro yet. Right-click the new workflow canvas and select Insert > Macro.  Any Macro that is currently open in Designer can be added this way to the workflow. Note that it is also possible to create a Macro repository. This will enable to save Macros in one designated place and easily use them in workflows. See this Help Doc article .  Finally, add a Directory tool, and connect it to the Macro's input. With this configuration, you can import the Excel sheets of multiple XLSX files in the given input directory (you can see Regions North and South from the two Excel files).   Common Issues   The file is being used by another process Unable to open file for read: FILEPATH. The process cannot access the file because it is being used by another process. To resolve, close any other applications (typically Microsoft Excel) that are accessing the file Some of the columns are not imported, but in Excel the file looks fine Alteryx uses the drivers that come with Microsoft Excel to import the files. Sometimes 3rd party software does not write data in the correct Excel format.  To resolve, re-save the file in Microsoft Excel. It should now be correctly imported in Designer.   How To: Input a Specific Excel Range Another functionality of Alteryx Designer is the ability to input a data subset of an Excel file. This comes handy when working with large data.   The procedure to input an Excel range would depend on the Designer version.   Prior to 2018.1 In this example, our data starts in row 5 and column B and ends in row 7 and column D.       Bring the Input tool into your module, then browse to the particular sheet in your Excel file that you wish to pull data from. It will look like the following image.  Notice that Option #3,  Table or Query , points to  ‘Sheet1$’ , we will modify this to point to our data range. To edit the Table or Query , click on the button with three dots ( … ) on the right side of Option #3. Click the SQL Editor button and change the range to SELECT * FROM 'Sheet1$B5:D7'  Click OK. Now click on the Update Sample  link In the Input Tool properties window to see the new range.       2018.1 and later     In versions more recent than 2018.1, the button with three dots (…) on the right side of Option #3 cannot be used anymore. Instead, click into the file path field, and edit it.  Use the following syntax filepath.xlsx|||'SheetName$RangeCell1:RangeCell2'. For example: \sample.xlsx|||'Sheet1$B5:D7' imports the Excel file sample.xlsx from Sheet1, and the range B5 : D7 . Note that the value of 3. Table or Query has changed. Additional Resources   The Dynamic Input Tool Mastery article contains valuable insights on practical use cases of this powerful tool. A more general take on inputting data can be found in this insightful Knowledge Base article. Fun Fact  Bananas grow on plants that are officially considered a herb since the stem does not contain woody tissue.

  With an Input tool alone, you cannot specify multiple ranges within an Excel sheet to import at once, however, i t is possible to achieve this within Alteryx using a slightly different process!   The tool you want to use to accomplish this task is the Dynamic Input tool. The process is quite simple. Simply enter your Excel ranges into a Text Input Tool, complete with Sheet name and tick marks (Shift + ~), with one range on each line:     Connect your Dynamic Input to the Text Input and select the file you want to pull the ranges from. Set up the input as you would any standard Input tool to read a range. When doing so, be sure to check the option indicating that the First Row Contains Data (we'll rename the headers later). Using this option avoids rows being skipped due to a mismatch in the header fields:     For more information on the specifics of doing this, take a look at this article.   Once this is done, continue configuring the Dynamic Input tool. Choose the option to  Modify the SQL Query and click the 'Add' drop down. Select the option to "replace a specific string", and remove all text except for the sheet name and range:     Once you are finished here, bring down a Dynamic Rename tool, and select the rename option that says "Take Field Names from Frist Row of Data". This option will move the first row of data into your field headers, reversing the effect of choosing the option for First Row Contains Data:     Finally, run the workflow and enjoy your newly unioned file!   The attached sample demonstrates the process using dummy data. You'll just need to repoint the Dynamic Input tool to the sample data spreadsheet.

How to dynamically run the most recent file in a file folder   Sometimes you may have daily, weekly, monthly or yearly data dumps where you want to only run the most recent file. Within Alteryx you can make this process dynamic and seamless through the use of a few tools.   Step 1: Directory Tool The Directory Tool will allow you to browse to a folder and return all the metadata related to the files which exist within that folder. The field of interest in the metadata is the 'Creation Time'.    Step 2: Sort Tool Using the field called ‘Creation Time’ we can use the Sort Tool to sort the date and time values into descending order to get the most recent file at the top of the dataset.   Step 3: Sample Tool After sorting the 'Creation Time' field I now have my most recent file in record 1. Yet, I still have rows of data for the other files within that folder that I need to remove. I can now use the Sample Tool to take the ‘First 1 Record’ and this will result in the latest file information being left.     Step 4: Dynamic Input Currently the field I have in my dataset only shows metadata avaliable for that file such as Full path, Creation Time etc. I now need to read this file and pull in the data by using the Dynamic Input Tool. In the ‘edit’ section select a placeholder file.   Then in the Read a list of data sources ‘Field’ dropdown this will need to be the ‘Full Path’ field coming from the directory Tool. In the Action dropdown this will need to be set to ‘Change Entire File Path’.     Step 5: Run the workflow You can now run the workflow and it will dynamically always pick the latest file from that folder and read the data into Alteryx.   Please find an example workflow attached to this article.

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.

  Here in Customer Support, we like to make sure our workflows run as smoothly as possible. Sometimes you may ask yourself “Why can’t my excel file just open right after I output it?”   Where there is a will, there’s a way.   Ladies and Gentleman, I introduce to you the beautiful functionality of the Run Command Tool.   With this trick, you never have to go into the directory to manually open the saved file again.   Note: There are multiple ways of doing this with the run command. This is just a simple example.       For this trick, you’ll only need 5 additional tools.   Before your output tool, you’ll need a Block Until Done Tool. This is for quality control as Excel doesn’t like to open when you are writing into it at the same time.   This prevents Alteryx from writing and opening at the same time.   Next, you’ll need a Formula Tool, which will just add another field with your command. When making this dynamic, you’ll want to make sure you update both the path of your output and formula tool.       Whether you do that in a macro or app, it’s entirely up to you. After the formula tool, you’ll want a sample tool so that way we only select tool to deselect all the fields except for your command. Now just make sure your run command is set up properly. In this example, it’s set to write out the command as a bat file and be read back in as a command.       To learn more about how to configure the run command, I would read through this helpful article.   After you have this set, watch as your workflow has made your life even more automatic.

Some users have reported a problem when importing and exporting macros within the Alteryx Designer when the Regional Settings for the machine are not set to English (United Kingdom) or English (United States) (see screenshots below). You may also be unable to see personalized Macro folders within the Designer. They encounter the following error: "Input string was not in a correct format.”   The workaround for this is to switch the Regional Settings to the regions named above and you will then be able to import, export macros and see personalized macro folders within the designer categories.   The screenshots below demonstrate what the users will see when they try to import and export workflows with other Regional Settings than English (United Kingdom) or English (United States).               To find Region and Language settings in Windows 7, please navigate to the Control Panel>>>Clock, Language and Region>>Region and Language. For more information on Localization please see this link: http://community.alteryx.com/t5/Analytics-Blog/Alteryx-Commences-Localization-Endeavors/ba-p/11765.        

This article is part of the CS Macro Development Series. The goal of this series is to communicate tips, tricks, and the thought process that goes into developing good, dynamic macros. In this part, we demonstrate how to read in multiple files with different schemas using a Batch Macro.

Question Can I read in an Excel file located in a zipped archive file from Amazon S3? Answer Unfortunately, this is not an option within the Amazon S3 Download Tool, as it only allows you to choose between CSV, DBF and YXDB files. However, this is possible within Alteryx with the use of a simple workflow utilizing a three line batch file, the Run Command Tool (master it here), and the AWS Command Line Interface (CLI).   In order to use the CLI, you must first download it and configure its settings. Please visit this page for information on how to do that. Once that is setup, you simply need to setup the batch file and configure the Run Command Tool.   Step 1 In the first step, you will use a Text Input Tool to write the batch file code. This code will use the CLI to copy the ZIP file from the S3 bucket to a locally accessible drive. Configure the Text Input Tool as follows:     Important: Make sure that line 2 points to where your CLI is installed. In line 3, replace "alteryxtest" with the name of your bucket, "ExcelTest.zip" with the name of your ZIP file and enter in the correct location to copy the file to.   Step 2 In the second step, you will use the Run Command Tool to do the following: Write out the batch file ("Write Source") Run the batch file created in the previous step ("Run External Program") Read the file into the workflow ("Read Results")   Important:  When entering in the "Read Results" section, your ZIP file will not exist at this point so you cannot simply navigate to and select the file. So, you have two options: Click on the "Input" button and enter in the full path of where you are copying the ZIP file (found on line 3 of the Text Input tool) along with the file name, a pipe character, and then in brackets, the sheet name. For Example: C:\Users\dchapman\ExcelTest.zip|ExcelZIP.xlsx[sheet1] Run the workflow once without the "Read Results" section completed in order to copy the ZIP file from the S3 bucket. Then, click on the "Read Results" button and navigate to the ZIP file and choose the Archive file to read it.    This same workflow can be used to read other archived files as well. However, you will have to make slight adjustments to the "Read Results" section of the Run Command tool. For example, if reading in a CSV file, you would simply include the archived file name. Since a CSV file does not have "sheets", the bracketed sheet name is not needed.   I plan to create a simple macro with a user interface that will do the same thing. Once complete, I will post it in the reply section.   Thanks for reading!

Here is a simple tip to create a Text Input tool with multi-line records.

Set yourself up for success with excerpts from the 2017 Tips and Tricks Book for bringing data into your workflows!

A relative path is in relation to the location of the App on the users system. Check out our examples of how and when to use them!

Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once. In this case, the preferred alternative is to use the Dynamic Input tool.