community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Parsing With Commas and Spaces Embedded

Meteor

Hi Folks,

 

I have some data that look something like this:

 

Love Me Tender 3.4 5.6 7.8 9.7

Shake, Rattle & Roll 2.1 3.2 5.4 6.5

Put a Ring on it 4.3 2.1 7.6 5.4

 

There's a comma after "Shake" and nowhere else.

 

I'd like to use the Text to Columns tool to parse all these values into five columns but between the comma in the title and the spaces between the fields I'm having trouble coming up with an approach to parsing this.

 

Any ideas?  Thanks.

 

Alteryx Certified Partner

@FlyerFocus 

It will be really hard to parse this with a Text to Column tool.

The best way to parse is using Regular Expressions to identify the patterns in this data and split them into separate fields using the RegEx tool.

If you don't know regular expressions, you are basically trying to identify patterns for the different pieces of data and regular expressions use various characters to specify the pattern:

The pattern in your data:  words and spaces divided by a space, then a number with a decimal place and number after that, then a space, then a number with a decimal place and number after that, then a space, then a number with a decimal place and number after that, then a space, then a number with a decimal place and number after that, then a space.

The regular expression to parse out the data:  (.*)\s(\d\.\d)\s(\d\.\d)\s(\d\.\d)\s(\d\.\d)

(.*) - words and spaces (well.* really means any character at all, one or more times)

\s - a space

(\d\.\d) - number with a decimal place and number after that

\s - a space

(\d\.\d) - number with a decimal place and number after that

\s - a space

(\d\.\d) - number with a decimal place and number after that

\s - a space

(\d\.\d) - number with a decimal place and number after that

you can learn more about regex in the academy and other discussion posts.  Here are a few posts (there are more):

Attached is a workflow that parses the data out with RegEx.

 

Highlighted
Bolide
Bolide

The only other way than Regex that Maureen mentions (the way i would do it) is to reverse the string "ReverseString(String)", then pass it through the text to columns tool with \s as the delimiter and set it to 5 columns (assuming it will always be a title followed by 4 sets of numbers separated by spaces). Then use a multi-field tool to reverse the text back to normal. All the fields will be switched as well, so you'll have to account for that. See the attached workflow.

Quasar

Absolutely love @Dynamomo 's power of RegEx 1 tool solution!

 

I just wanted to add the method that I posted in another question of yours. Works in this case too (just not as elegant!)

Meteor

Dynamomo,

 

Very clever solution, at least to this here newb!  I was able to adapt it to my data set.  Thank you for the help!

 

One question--I have seen other workflow outputs where the input data column is suppressed in the output.  In this case how can Field 1 get suppressed in the output?  I poked all around in the parse tool and I don't see an option for that.  Also tried the Arrange tool.

Alteryx Certified Partner

@FlyerFocus 

If you mean that you want to remove fields before you output, you use the Select tool for this.  Deselect the original field to remove it from your output.

 

Labels