Alteryx Designer Knowledge Base

How to collect Wireshark traces to troubleshoot network-related issues.

How the 'Group Replacement Value for SQL IN Clause' works when using SQL: Update WHERE Clause in the Dynamic Input Tool

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. 

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.

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!

One of the biggest reasons why people love Alteryx is that it has the ability to read a very large number of different data sources. This article includes a workflow that is able to read in non-natively supported formats like a Word doc or pdf by using a open source program to convert these formats to plain text. 

We've had a few questions on importing fixed width files within Alteryx. Here is a quick 'how to' guide in reading in un-defined file formats!

In this article we’ll take a look at how to leverage a batch macro to read in multiple sheets from multiple Excel files using Designer version 10.0. The two Excel files I’ll be using in the example are called Fruits and Vegetables with sheets named Apples, Oranges, Broccoli and Spinach, respectively.   The first step will be to read in the list of sheet names from one of the Excel files using an Input tool. This feature is new to 10.0 and is a great addition when used in conjunction with the Dynamic Input tool.   We can then connect our Input tool from the previous step to a Dynamic Input tool. In our configuration, use the same Excel file as our Input Data Source Template. We can use the Sheet Names field from our Input tool as our List of Data Sources and our action is going to be to Change File/Table Name.   Now we’re ready to add our Interface tools to the canvas. Bring in a Control Parameter tool, 2 Action tools and a Macro Output. Connect the Control Parameter to the 2 Action tools, and then connect on to the Input and Dynamic Input. The Macro output will be connected to the output of the Dynamic Input.   In our Action tool configuration our action type will be Update Value. We want to update the File – value that is being passed on to the Input and Dynamic Input. We will, however, only need to update the portion of the string without the sheet name, which is why we’ll also select Replace a specific string: This will allow us to batch our process with multiple files.   Lastly, if our 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. We’re now ready to save the macro and put it to use!   Now, on a new canvas, we can place a Directory tool (If all your Excel files are in one directory), or, we can use a Text Input to manually enter each individual file path location. After the Directory or Text Input tool, insert your macro (right click on the canvas->insert->macro), then choose the full file path for your question in the macro configuration. Add a browse, hit run and look at the results!     We now are able to read in multiple Excel sheets from multiple Excel files!   The attached workflow package includes a workflow, batch macro and excel files created in Alteryx v.10.0   Tony Moses Client Service Representative  

When importing a delimited file, have you ever run across an error saying ‘Too many fields in record…’ and it’s something like record number 1478254 and you think to yourself, “how the heck am I going to find that record and correct it?”   Well, let us help you with that.   This message is essentially telling us that some particular row of data, when parsed, has more fields that the first row of data (usually caused by an extra delimiter in that row). The first step in identifying the row is to change the delimiter in the Input Data configuration window. Change the delimiter from the current delimiter to \0 (that is a backslash zero, which stands for no delimiter). This will read in your file but instead of parsing, it’ll just read in the data as is.   Current delimiter:   Versus:   No delimeter:   Now just add a RecordID tool and Filter tool so we can filter on RecordID 2 (or 1478254) to find the row that is holding us up.     Now that you’ve identified the row that is causing issues, you could go back to your original data, correct it and try importing it again.    If you were looking closely at the example data, you may have noticed that record 4 also had a different number of fields as row 1.  After correcting row 2 and importing , again, we would get an error for row 4. Just follow the same procedure to correct additional errors.  This could probably be done through an automated/iterative process, but that will have to wait for another time.   (attached is an example workflow created in Alteryx 10.0)  

Sometimes you only need to read in specific fields from a database or input. Within Alteryx it is as easy as using a Formula tool, Dynamic Rename and Dynamic input!!   The first step is to bring in the master file and the list of field names you wish to carry through from the Master file.   Next attach a formula tool to the field names list and create a new field with the expression "Keep-" + [FieldNames].     This will rename the fields with “Keep-” appended to the field name.   The next step is to use a dynamic rename tool to “Take Field Names from Right Input Rows”. In the 'New Field Name from Column" select the field which has just been created above.   Use a dynamic select tool to ‘Select via a formula’ and use the expression below to find the field which has ‘Keep-“ appended to the left hand side.  This will then only bring through the specific fields from the Master file which you have listed in the field name input.    Using a dynamic rename you can return the fields to their original names through a substring function. This will remove the "Keep-" from the field name.       You now just have the fields you want from the Master input file!      Jordan Barker Client Service Representative

These methods should work in most versions of Alteryx.    One of the most common issues we see from clients trying to read in a .csv file is that they are receiving an error starting “Too many fields in row x”, and because of this Alteryx can’t read in the file.     There are a couple of different ways you can resolve this error.   First, in the Input Data tool, you can tell Alteryx to treat read errors as warnings to allow the file to be read in.     This will change the Error to a Warning so that Alteryx can read in the file so you can investigate.     The other method is to read the file in with no delimiter. You can do this by changing the delimiter in the Input Data tool to \0.     Once you have your data parsed back out into its fields, you can use the Dynamic Rename tool to correct your field names, a select tool to remove the original field, and a simple Trim() function to remove the extra delimiter from your data.   This process is illustrated in the attached workflow, created in version 10.1.    

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.   

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).

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.

Connecting to Google Analytics is becoming more and more popular. There are a few things you need in order to use the Google Analytics macro; a Google Account (e.g., Gmail) and authorized access to an existing Google Analytics account. This article will help you get the rest of the way.

Inputting Data in Chinese, Japanese and Korean Characters

Can I read Outlook emails and attachments with Alteryx?

How To: Create an In-Database Connection

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 error is typically thrown when trying to read a file that does not match the selected File Format, but also in rare cases, from a corrupted file.