Parsing Data with RegEx

1. What are Regular Expressions (RegEx)?

You've likely used Alteryx's standard string functions (like LEFT(), RIGHT(), FINDSTRING()) in your Core certification work. These are great for simple, predictable tasks. But what if you need to extract an email address from a block of text, where its position and length can vary? Or pull all product codes that follow a pattern like "two letters, three numbers, one letter"?

This is where Regular Expressions shine.

A Regular Expression (RegEx) is a special sequence of characters that defines a search pattern. Instead of searching for a fixed string, you search for a pattern, giving you incredible flexibility to find, extract, and manipulate complex text data.

To get a solid introduction, please read the first few sections of the following article from phData.

2. The RegEx Tool in Alteryx

In Alteryx, your primary tool for this is, unsurprisingly, the RegEx tool, found in the "Parse" tool palette. It allows you to apply a regular expression to a column and perform one of several actions.

This image gives a high-level overview of the different ways the RegEx tool can operate. Today, we'll be focusing on the Parse method.

To understand the tool's interface, please return to the article you just started.

3. Parsing Data with Marked Groups

The key to extracting data with the RegEx tool's Parse method lies in defining marked groups (or capture groups). A marked group is any part of your regular expression that you enclose in parentheses ().

When you use the Parse method, Alteryx runs your full expression against the string, but it only outputs the parts of the string that match what's inside the parentheses. Each set of parentheses creates a new output column.

Let's look at a simple example.

Let's break down that expression, -(\d{4}):

• -: This matches the literal hyphen character. It's outside the parentheses, so it's required for the pattern to match, but it won't be part of the output.
• (): These parentheses define our marked group. Whatever is matched inside them will be extracted.
• \d: This is a special RegEx character class that matches any single digit (0-9).
• {4}: This is a quantifier that means "match the preceding token exactly 4 times".

So, \d{4} means "exactly four digits". The full expression looks for a hyphen followed by exactly four digits and then extracts just those four digits.

Now, let's see this in action with a video demonstration.

4. Building More Complex Expressions

Real-world data is rarely as clean as a ZIP code or a perfectly formatted phone number. Let's explore some more powerful RegEx syntax.

Let's see how to combine these in a more advanced scenario.

This example of parsing the description is particularly useful. The expression ([A-Z][^.]*\.) looks for:

• [A-Z]: A single uppercase letter to start the sentence.
• [^.]*: Any character that is not a period, repeated zero or more times. This is how it captures the middle of the sentence.
• \.: A literal period to end the sentence. The backslash \ is crucial here to "escape" the period, which otherwise is a special character meaning "any character".

5. Practice and Experimentation

RegEx can be tricky, and the best way to learn is by doing. Writing and testing expressions is an iterative process. A highly recommended free tool for this is Regex101.com. It allows you to paste your text, write an expression, and it provides a full explanation of what your expression is doing, highlighting the matches in real-time.

Your Turn: Practice Exercise

Imagine you have a field with the following text: "User 'jsmith' logged in from IP 192.168.1.100"

Your goal is to parse this into two separate fields: the username (jsmith) and the IP address (192.168.1.100).

Try to build the expression in Regex101.com. Here's a hint for your expression structure:

User '(<group 1>)' logged in from IP (<group 2>)

Think about what syntax you would use for <group 1> (the username) and <group 2> (the IP address).

Solution Breakdown:

• Username: It's a series of word characters. \w+ is a good fit.
• IP Address: It's a series of digits and periods. A simple way is to match any character . until the end of the string. But a more precise pattern would be [\d.]+, which matches one or more characters that are either a digit or a period.

So, a complete expression would be: User '(\w+)' logged in from IP ([\d\.]+)

In the Alteryx RegEx tool, you would:

1. Put this expression in the editor.
2. Set the output method to Parse.
3. Two new output columns would be available to configure, one for the username and one for the IP address.

Conclusion

In this lesson, we introduced the fundamental concepts of Regular Expressions and their application in Alteryx. You are now equipped to tackle complex string parsing challenges that go far beyond basic string functions.

Here are the key takeaways:

• Regular Expressions (RegEx) are patterns used to match and manage text.
• The Alteryx RegEx tool is your primary interface for using RegEx, with Parse, Match, Replace, and Tokenize methods.
• The Parse method relies on marked groups () to extract specific parts of a string into new columns.
• Essential RegEx syntax includes character classes (\d, \w), quantifiers (+, *, {}), and character sets ([]).
• External tools like Regex101.com are invaluable for building, testing, and debugging your expressions.

This is a topic where practice makes a significant difference. As you continue through the course, look for opportunities to apply RegEx to clean and structure messy text data.

In our next lesson, we will shift gears from parsing unstructured text to reshaping structured data, learning how to pivot data between wide and long formats using the Transpose and Cross Tab tools.