Alteryx Designer Desktop Knowledge Base

How To: Read XLS files – with several tabs that contain diacritics Information

Querying a Salesforce report using the Salesforce Input tool returns a maximum of 2000 records.

When running a workflow via the Scheduler (for Designer with Automation), the following error is observed:

How To: Create an In-Database Connection

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.

How to troubleshoot an error opening "[filepath].yxdb": Data at the root level is invalid. Line 1, position 1.

The cause of this error is that too many delimiters were located in that specified record. For example if I had a text file with the contents below:

When importing a .xls file using the Input tool, Alteryx produces NULL values for specific cells, why does this happen?

Learn about upcoming changes to Alteryx support of 32-bit database connections and start planning today

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.

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.    

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

Inputting Data in Chinese, Japanese and Korean Characters

Can I read Outlook emails and attachments with Alteryx?