Parsing RFC4180 format .csv file with Alteryx

Hi,

I'm trying to read .csv file that is prepared in line with format RFC4180: http://www.ietf.org/rfc/rfc4180.txt Esentialy it means that if value has quotation mark or comma, gets wrapped in additional quotation marks. On top of that whole line (first and last value) get's wrapped in extra quotation marks. When i try to input these files in alteryx I either end up with all values in one field or with parsing error. I figured out Input tools setup that allows me to ignore delimiters in quotes or single quotes, but i still have values with comma inside that is considered as two separate values. I can't build RegEx that will be flexible enough to include both special characters so will appreciate any advice

Attached is file with dummy data so feel free to try it on your own.

Zus absences report for import.CSV

Developer Tools

Input

Regex

Accepted answers

gautiergodard

hey @marcinrasinski

Here is one way to do it:

Parsing.yxmd

All comments

Felipe_Ribeir0

Hi @marcinrasinski

Are you sure that this is a valid file? It seems strange to me, some fields have lots of ", some fields have only one.

gautiergodard

hey @marcinrasinski

Here is one way to do it:

Parsing.yxmd

marcinrasinski

Felipe_Ribeir0
I was also thinking something was wrong so I asked users to provide file even not opened by anyone and you see it. I only changed letters - quotes and commas are in the very same order. I'm still trying to confirm that and actually see original file, but still i wanted to make sure that workflow is bulletproof.

@gautiergodard

Makes perfect sense, thank you! I was focused on parsing whole file at once, and missed idea to separate rows and process them in two streams. Will test it on more cases

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703