Parse out a CSV

[Farriyajawed]

Hello, 

I am trying to parse out a csv in Alteryx with "," as my delimiter in text to columns tool. However, for some rows, it is stop parsing after a while. 

for example this is the input: 

22000,04/18/2023,04/18/2023,49239370-00,Bill - LOCKE SUPPLY CO.: 3/4" PEX BALL VALVE BRASS,Plumbing,,"Water Air, Plumbing and Electric",19351,APJ,10.78,,-3301.52

I have the output in the screenshot attached. it stops parsing after Plumbing and electric. 

Hoping to find a solution for this. 

[Farriyajawed]

While your solution seemed reasonable, the csv file had multiple line items with different numbers and a find replace option did not work. 

I got the data in a different format to work with but can't figure out the csv option. 

[jdminton]

@Farriyajawed 

Here are a couple of options. I copied your single line of data to recreate a set of data with different values. I only change the 3/4" for this example, but it should work with others. You may need to modify the regex to account for other ways you might see data, but this should work if it is similar and in the same field. Here is what the data looks like:  

The below method would be used if the data is already in Alteryx. I am using a formula tool to identify where the 3/4" or 6" type numbers appear (this is for easy review). The regex tool after replaces instances where a digit is followed by " and a space with "-xxx-". I just wanted that string to be specific and not used elsewhere in the data. The text to columns then parses by comma and the last formula tool puts the " back in after.

 

If the csv data is not already in Alteryx (i.e. you have an input tool, you can use  these configuration settings in the input tool to parse correctly.

See attached workflow package.