As part of the data prep process, you may need to read in a text file, or split a variable into a few new ones. This is because the text file/ variable contains a string of information, separated by a delimiter (e.g. – , / _ ). This will read this as one value and to have it as more than that you need to tell a program to split the field. In Alteryx this is done using the Text to Columns tool.

The example data below (Figure 1) comes from Challenge 2 of the Alteryx Weekly Challenges, within which the Text to Columns tool is used a, alongside the Cleansing tool and Formula tool to tidy up the data file. First thing, as always, is to load the data using an input tool.

Figure 1. Example text data

Text to Columns tool

This tool (Figure 2), found in the Parse section, ‘parses’ text from a column with delimiters into separate columns.

Figure 2. The Text to Column Tool

After adding the tool to your workflow, reading in the text data, specify within the tool which field you’d like to split out, how to split them out (the delimiter) and how many columns to split them out into (Figure 3). If you wanted to lose some of the info, i.e. if you weren’t interested in the date in the challenge example, you could put 2 columns in the tool and it wouldn’t return these as a separate field. For reference it keeps the original field there, which can be ‘unselected’ using the select tool later.

Figure 3. How to use the Text to Column tool

You now have the various data in different fields. You can also do this in Tableau – see my post about how to do this here.

To then tidy it up further and clean it for the challenge you can use a variety of tools. I personally then used: the cleansing tool to get rid of the quotation marks from the book titles, as well as any leading white space; the text to columns tool to split the quotations from the date column; a formula tool to get Alteryx to read the values as a date (creating a new variable); a select tool to rename the other fields and get rid of unwanted fields and finally a browse tool to have a look at my final output (Figure 4).

Figure 4. My solution to Alteryx Weekly Challenge #2