Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data input text to columns

FECL3
8 - Asteroid

Hi

 

I have a csv data input that has irregular delimiters. Most of them are separated by commas, however some cells include commas within themselves. As a result it splits the data out incorrectly. Please see sample data in spreadsheet attached and desired output. 

 

How do I solve this?

 

 

10 REPLIES 10
atcodedog05
22 - Nova
22 - Nova

Hi @FECL3 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1605361592907.png

Workflow:

atcodedog05_1-1605361608987.png

 

Hope this helps 🙂 Let me know if this works for the larger data.


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

FECL3
8 - Asteroid

Thanks for the workflow, I can't seem to extrapolate it to my data source. Do you have a useful online resource that explains the regular expression in the "Format to convert" box?

atcodedog05
22 - Nova
22 - Nova

Hi @FECL3 

 

You will be able to find the meanings/usage of each symbols here.

atcodedog05_0-1605363583733.png

But building the expression is based on logic

 

Here is the explanation of the regex i am using. As we already know we want to split it on commas using brackets like () will catch part of the string.

 

(\w*),(.*),(\w*),([\d/]*),([\d/]*)  split

 

(\w*)  - Alpha + numeric zero or more times

(.*)    - Any character zero or more times. This takes in commas and space too.

(\w*) - Alpha + numeric zero or more times

([\d/]*) - Numeric + /(backslash) to accommodate dates

([\d/]*) - Numeric + /(backslash) to accommodate dates

 

This might help 

https://community.alteryx.com/t5/Interactive-Lessons/Parsing-Data-with-RegEx/ta-p/441415

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

 

If you tell me whats the issue you are facing i can help you out.

 

atcodedog05
22 - Nova
22 - Nova

Happy to help 🙂

 

Cheers and Happy Analyzing 😀

FECL3
8 - Asteroid

How would I parse it if there was a "-" in the middle of one of the cells. e.g. "....,Arizona - Phoenix,......" and I want the output to be "Arizona - Phoenix"?

 

Same question for if there was a "." in the middle of one of the cells. e.g. "A.I.M. Inc" and I want the output to be "A.I.M. Inc"?

 

The data in confidential and so I cannot share 

atcodedog05
22 - Nova
22 - Nova

Hi @FECL3 

 

For first case you can use  ([\w\s-]*) for second you can use ([\w\s\.]*) check and let me know.

 

It also vastly depends on the text before and after check the above and see how to accommodate it.

 

Hope this helps 🙂

StephaneP
Alteryx
Alteryx

Hello @FECL3 

 

RegEx is really a powerfull tool. And I love it so much. But if you are not comfortable with the regular Expression (we all are at the begining 😀 ) you can use an easiest way with the Text to column tool.

StephaneP_0-1605394859205.png

 

This tools is here to split one source column (here "Field1") in N target columns (here 5). To know what is in each of these column it is looking for a Delimeters (here our comma).

StephaneP_1-1605394903115.png

So regarding questions you are asking it should work as soon as there is no comma in your fields but only as a separator.

 

Then you have to rename your 5 columns.

And it gives the same result than @atcodedog05 

 

Hope it helps

Stéphane Portier
Sales Engineer
Alteryx
atcodedog05
22 - Nova
22 - Nova

Hi @StephaneP 

 

Apparently in his first scenario there is commas (,) in the value of coloum. Hence regex was a suggestable idea 🙂

StephaneP
Alteryx
Alteryx

Yes you are right, If there is a comma in the value of the column, Text to column will not work as expected and you have to discover the magic of Regex.

By the way here is a web site to test and understand your RegEx code. Very usefull. It explains how is interpret your code. Very funny sometime to realize how far you are from what you expect. 😋

https://regex101.com/

 

Another one that I haven't tested in detail but looks funny: You write in plain english what you want to do and it translate it in Regex 

https://losslesshq.com/

 

But the better trick of course is to have your own pet to help you.

RegEx.PNG

Stéphane Portier
Sales Engineer
Alteryx
Labels