RegEx for the Win: XML Tag Clean-Up

[Samantha_Jayne]

Overview

Use Case (Alteryx on Alteryx)

So working across borders and getting myself in deep community data, I was asked by my fellow colleagues Nicole Johnson (@NicoleJ) and Will Machin (@WillM) to look at the community data and report back on a few items. However, it was clear that reporting community data was not going to be just grabbing the fields from the database.

This use case was about taking data that looks like this:

<P><U><STRONG>WHAT?</STRONG></U></P><P>We’d like to run a challenge for Inspire attendees to submit workflows and use cases that can fuel our starter kits with real world solutions. This is an attendee challenge for every Alteryx skill level!</P><P></P><UL><LI><U>Are you a prospect</U> "kicking the tires" to see if Designer can solve your problems?</LI><LI>Attend a session or visit the Solution Center and submit that solution you built during training!</LI></UL><P></P>

And changing it to this:

WHAT? We’d like to run a challenge for Inspire attendees to submit workflows and use cases that can fuel our starter kits with real world solutions. This is an attendee challenge for every Alteryx skill level! Are you a prospect "kicking the tires" to see if Designer can solve your problems? Attend a session or visit the Solution Center and submit that solution you built during training!

Now I know RegEx is not everyone’s favourite tool to use, as it does require some syntax knowledge or a play on regex101 (there is no shame in this!). However, I wanted to show you all how easy it is to remember a simple syntax that can really help you clean up your data.

Using the Replace Method within the RegEx tool, with the following expression: <.*?>  and replace with nothing, i.e., don’t put anything in the box, like so…

…will provide you with the following output:

WHAT?We’d like to run a challenge for Inspire attendees to submit workflows and use cases that can fuel our starter kits with real world solutions. This is an attendee challenge for every Alteryx skill level!Are you a prospect "kicking the tires" to see if Designer can solve your problems?Attend a session or visit the Solution Center and submit that solution you built during training!

This is fantastic; using one tool, we can make this a lot more readable. However, it is obvious to see that we need to work a little more to make this grammatically ok to read. If you are anything like me, spaces are needed after that punctuation.

So back to the RegEx tool, now instead of Replacement text being nothing, just pop your cursor in there and add a space. The config won't really look different from above except that you know you have added a space 😉

Now add a data cleansing tool and for the column of body, select remove duplicate whitespace.

TADA:

WHAT? We’d like to run a challenge for Inspire attendees to submit workflows and use cases that can fuel our starter kits with real world solutions. This is an attendee challenge for every Alteryx skill level! Are you a prospect "kicking the tires" to see if Designer can solve your problems? Attend a session or visit the Solution Center and submit that solution you built during training!

This is fantastic and ready to use within reporting after just two tools. Never shy away from using data from the internet again!

Need to see it in action? Download the attached example workflow and have a go.

RegEx for the WIN - XML Tag Clean Up.yxmd

[mceleavey]

Regex makes me happy.

[MarqueeCrew]

@Samantha_Jayne ,

A wise @Ned told me that RegEx was "Write Once.  Read Never."  My favorite expression is:

[ -~]

and a close second is:

[~ -~]

Your expression brought a smile to my face too (as it did for @mceleavey ):

<.*?>

Alteryx offers multiple tools that  ...  I am sorry, but I just ran into @MaureenW in the lobby of the hotel that I'm staying in and we just caught up.  I love Alteryx and Alteryx Community for bringing so many wonderful people into my life.

Alteryx offers multiple tools that empower users with RegEx support.  I was reading your post (at the airport to this trip to Denver where I ran into Maureen who was on her way to meet Ned for a bike ride).  There are so many connections and ways to mix and match both solutions and random thoughts that one can never really choose what is "Best".

I understand the use of the data cleanse tool to complete the whitespace removal, but i would nest the cleanup (the data cleanse is going to use RegEx to do it) with your RegEx and move this to a formula tool.  The RegEx tool handles some things better; but in this case I'm partial to the formula tool.

I won't write the expression to cleanup the tags and remove the whitespace here.  Maybe someone else will chime in with their preferred approach.

I know that all of this is a bit chaotic, but I'm waiting for @T_Willins to go for coffee ☕.  

There's always data cleanup and I'm glad that you are on duty not only cleaning up the community, but most importantly you are sharing you knowledge as you clean it up.

Keep it classy.

Cheers,

Mark