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. 

[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.

[Farriyajawed]

I Understand - No one touched the data. It was a direct extract from the system. 

[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?