Parse out a CSV

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.

output csv .png

Best Practices

Accepted answers

All comments

Raj

@Farriyajawed
while using Text to Column,

check the advance option, ignore delimiters in Quotes.

this will solve the problem.please maek it as done if this solves the problem.

apathetichell

ignore delimeters in quotes.

Farriyajawed

I am using the ignore delimiters in quotes option but still getting the same problem.

quotes.png

aatalai

@Farriyajawed increase the number of columns, does that help resolve the issue?

Raj

@Farriyajawed this works perfectly fine.

777.yxmd

Farriyajawed

Raj, you workflow leaves everything in column 6 whereas everything needs to be parsed and" -3301.52" should be in last column

Raj

@Farriyajawed i reffered to the screen shot you added.
kindly add the required output, will help

Farriyajawed

Here you go!

apathetichell

You have an unmatched quote. Your data is wrong. You cannot do what you are doing with an unmatched quote.

change:

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

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

corrected data and corrected workflow.yxmd

apathetichell

Can you mark this and ignore delimeters in quotes because that is the correct advice. Also - please mark the workflow provided by @Raj as correct.

apathetichell

Any update here? Are we all on the same page that the issue is that someone messed up your data and took a quote out - looking at you microsoft excel.

jdminton

I don't think anyone "messed up the data". This is a description that contains the double quote character in place of the word "inches". I've run into these before and you will need to do something to the csv file to replace that character with a find replace for something like 3/4" to 3/4** or something and once you've brought into Alteryx, you can replace the 3/4** back to 3/4". There may be other descriptions that will cause issues though. If you can modify the source to use a different delimiter, like pipe (|), that would be better.

apathetichell

@jdminton - 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

should be

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

in the original Bill - LOCKE SUPPLY CO.: 3/4 has an ending quote - but no beginning quote. This is not system generated data.

Hypothesis - a human touched this data. Then (perhaps another human) tried to load this into Alteryx and got frustrated because ignore delimters in quotes was not working. A bunch of people spent some time trying to identify why the ignore delimiters in quotes wasn't working. It wasn't working because the data was broken.

do you think a system outputs inches as single "s while keeping " as a text with a comma separator? It's totally possible. Weak error checking.

Adriankp

There are unfortunately plenty of systems worldwide where there are no checks for whether or not quotes and/or delimiters exist in free-text fields when data is exported. I encounter it quite often when working with data from small regional ERP systems from all around Europe.

jdminton

@apathetichell that is not correct

“in the original Bill - LOCKE SUPPLY CO.: 3/4 has an ending quote - but no beginning quote. This is not system generated data. “

LOCKE SUPPLY CO is the vendor name. 3/4” PEX BALL VALVE BRASS is the description of an item.

jdminton

@Farriyajawed @Please see my note above about delimiters

apathetichell

I'll blame inefficient ERP systems and apologize for casting aspersions on the lone quote. @Farriyajawed as per @jdminton 's recommendation - your best solution is to first handle the inches quote - and then parse normally as quotes within delimiters. I'd recommend some kind of regex to identify '\d/\d(")' and replace that with \|

jdminton

@Farriyajawed did that resolve your issue?

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.

Parse out csv.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703