How to Tokenize data in Alteryx?
In this blog post, I will explain how to use the Tokenize option within the Regex Tool in Alteryx.
The example I’ll use is based on one of my Friday projects, where we had to perform web scraping to download data from a website and manipulate it for reporting purposes. I decided to scrape all the games that my favorite team played. Below, you will find a screenshot of the website displaying a table with all the game information. Although there are many steps involved in processing this data with Alteryx, I will focus exclusively on the Regex Tool.
Regex:
Regex stands for Regular Expression. It is a powerful tool used to parse, match, or replace data using defined patterns. In Alteryx, the Regex Tool provides several output methods, including Parse, Match, Replace, and Tokenize.
Before applying the Regex Tool, I inspected the website and found the table structure. In the example below, I will highlight the Attendance data. This was the format of the table before applying Regex.
I first selected the column that I wanted to parse, called “DownloadData”, where all the relevant data is located.
The structure of the HTML table dictates the Regex expression. Here’s a breakdown of the pattern I used:
Regex Pattern: (.*?)
Explanation: Matches the opening tag of a cell where the attendance data is stored. (.?): This is a non-greedy capture group that captures everything between the opening tag and the closing tag. The .? means “match any character (.) zero or more times (*) in the shortest way possible (?).” : Matches the closing tag of the cell.
For example, if the cell contains: 91,438 The extracted value would be 91,438.
I chose the Tokenize method to split the data into multiple rows. The Tokenize option is useful when you have repeating structures in your text that you want to separate and process individually.
This is the result after using the regex parse tool:
I used the Tokenize method to split the text into rows. This allows me to break down the HTML structure and extract the relevant data from each row separately. To get all the necessary information, I used three different Regex tools, each designed to parse different parts of the data.
This is the final table:
I hope you enjoyed this content. See you next time.