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

Replace commas in a CSV file with a blank

murthybhs
8 - Asteroid

Hello amazing people at Alteryx community,

 

I have a CSV file data that looks like this.

murthybhs_0-1616549732362.png

If an trying to use the Text to Columns tool in the Parse tool palette to split this based on the comma (,) delimiter. However, upon close observation, we can see that some companies that have Inc, LLC have a comma in between them. How do I prevent Alteryx from splitting the company name?

 

Any help on this is much appreciated.

7 REPLIES 7
estherb47
15 - Aurora
15 - Aurora

Hi @murthybhs 

I'd take a two-step approach to this challenge. 

First, use a text to columns with a comma delimiter, but separate into two columns only. That will get the name out.

Then you can use a RegEx tool to separate the rest. 
Use the parse method on the second column (the one that now starts with the company name), with the following expression: 

(.+),(\d+),(\d+)

 

The first group captures the first bit of text up until the comma that precedes the numbers. The second and third groups capture the numbers.

 

Let me know if that helps.

 

Cheers!

Esther

MarqueeCrew
20 - Arcturus
20 - Arcturus

@estherb47 ,

 

 how about a slight one-step modification?

 

(.*?),(.*),(\d+),(\d*)

 

 the second grouping is greedy. It takes care of inc.  

 

cheers,

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
estherb47
15 - Aurora
15 - Aurora

@MarqueeCrew 

 

Indeed, greed sometimes does pay! That works too. Love solving together.

murthybhs
8 - Asteroid

Where should I use this? In the RegEx tool?

estherb47
15 - Aurora
15 - Aurora

@murthybhs 

 

Yes. Both options will work in the RegEx tool. @MarqueeCrew 's only uses the RegEx tool, and mine uses text to columns first, and then RegEx.

 

Cheers!

Esther

MarqueeCrew
20 - Arcturus
20 - Arcturus

Yes @murthybhs ,

 

 like @estherb47  mentioned, mine is designed for the RegEx tool in parse mode.  But if you prefer to use a formula tool you can use it with :

 

regex_replace([your field],"(.*?),(.*),(\d+),(\d)",'$1|$2|$3')

 

 you can then take it into a text to field tool as a pipe (|) delimited string. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
murthybhs
8 - Asteroid

Thank you for the reply @EstherB47 and @MarqueeCrew. I was able to achieve this by using the Find Replace tool. I used " , INC " in the Find and "INC"
in the replace portion. 

Labels