Converting Data Types in Alteryx

One of the most common tasks in any ETL project is converting data types.  When data begins in the form of a text file or (even worse) a formatted excel file, one often has to load the data as text fields initially and convert to proper data types after parsing out the relevant data.

Thankfully Alteryx has a lot of built in features to facilitate this.  One is the Multi-Field tool of which I gave an overview in my earlier post.  This handy tool lets you apply the same function across a whole set of columns without having to laboriously replicate it separately for each column as you must do in SQL select statements.

In many cases, however, one doesn’t even need to use any explicit data conversion tool in Alteryx – instead, you can just use the handy select tool which let’s you easily specify the data type you want while also allowing you to rename fields.  In the screenshot below, I’m using a select tool to convert generic field names (i.e. Field_1, Field_2, etc.) into more descriptive fields while at the same time converting them to the desired data type.

While this tool works great most of the time, one always seems to run into a catch eventually as I did the other day.  I was trying to load some data from CSV files that had extremely precise numbers stored as text (such as “53.534859348793489433498”).  When I tried to use the simple select tool to convert these text fields to numbers, I received an error message at run time that the field I was trying to convert “had more precision than a double. Some precision was lost.”

One of the handy things about Alteryx is that even when it hit’s a bump like this along the way, it usually notifies you of the situation via a message in the execution logs but keeps going despite the error.  In my case, I was happy to rounding these numbers a bit, so I was fine with proceeding.

Since I never like seeing warnings in my logs, however, I decided to come back to this later and find a better way of dealing with these conversions.  With some help from the Alteryx community, I turned to the second set of data conversion tools in Alteryx.  These are the conversion functions accessible via the formula tool.  As you can see below, these include all kinds of functions for converting data types.

In my case, I needed to use the ToNumber function which include an optional bIgnoreErrors variable.  When you enter a 1 for this variable, it suppresses the “precision lost” error message I was getting earlier – exactly what I wanted.

The final step was to apply this function to all thirty of my measure columns.  This was easily accomplished using the Multi-Field formula tool.  As you can see below, all I had to do was select all the fields I wanted to convert in the upper box, specify the new output data type I wanted, and use the ToNumber function applied to the _CurrentField_ down in the expression area.

With that simple fix, I was off to the races with error-free data conversion.