Unescaped Quote in CSV file

Hello all,

I am a newbie (so be kind).

I have a CSV file I am importing to ServiceNow to be transformed. The source file fails to process in ServiceNow.

I am using Alteryx Desktop Designer (file input is CSV) and run; I can see the data fails at each row with an error (see the attached "enescpated error.jpg" image).

My steps (I reviewed and tried ~10 forum solutions) are to find/replace the double quote and then output to CSV.

However, when the data is output back to a new CSV file, it fails to wrap a leading/ending double quote around each row, which causes issues with the expected import (see

If I add a formula to add the double quote, I get double double quotes.

Any recommendations would be appreciated.
-john

enescapted error.jpg

some have double quote some dont.jpg

Developer

Accepted answers

NeoInfiniTech

Hello @jemcconnell, please take a look at the attached workflow. As this is a case I previously encountered many times, I hope this workflow which I built based on the sample mock-up string you shared here will be of help, though you will have to carefully read the workflow and adapt it to your own data.

The problem here seems to be that the quote sign used is " and inches are also specified in the data with ", additionally the separator is comma and the descriptions also includes comma, which makes it difficult to parse the data properly. While the best solution would be to have the data extracted with a different delimiter and quote sign (or a rarely used characted for the delimiter such as ‽ so that the quote sign won't even be needed), not all the systems have the capability to output data with such options.

One caveat for this workflow is that this will most likely work properly only if there is one field that is problematic (which is hopefully the case) as I never tried this on a data that has more than one problematic fields.

What I did here is to split the data until the problematic field using Text to Columns, then did the same for the reversed version of the data, then subtracting the field and further process the data (if necessary) before creating the final output. This also helps preserve the comma and quote signs that are in the data.

quotes_and_commas_in_text.csv

Quote Delimited Text Containing Quotes.yxmd

edited_data.yxdb

All comments

apathetichell

on your input data make sure you have ignore delimiter in quotes. In your output data make sure you have your quote mode set up to quote delimiters. Make sure you are using the correct delimiters - and the correct format. Also turn off AMP - and make sure you are bringing in the correct amount of characters (ie more than 255). If you still see that error- set your delimiter to \0 - use a record id - look at the problem rows and see why they are giving you issues.

jemcconnell

Thank you for the quick reply.

The data is garbage. Plan and simple for sure.

File in (errors with unexcapted quote):

F1,F2,F3,F4,F5

"","1","this is, and this is 2" and >5", but not 6"","0123"

This format has the advantage that data with a comma may exist; using this suggested method, including 0 as a new delimiter, causes more errors.

File out:

F1,F2,F3,F4,F5

"",1,this is, and this is 2" and >5", but not 6",0123

I attempted both suggests and no luck but keep tying.

apathetichell

did you use 0 or \0?

jemcconnell

Hello, @apathetichell

I used the \0 (slash zero), as the delimiters.

This could work well if it were not for the commas in the field and the extra double quotes, it works well.

NeoInfiniTech

quotes_and_commas_in_text.csv

Quote Delimited Text Containing Quotes.yxmd

edited_data.yxdb

jemcconnell

Hello @NeoInfiniTech

Thank you for the reply. Yes, this is a mess and confined to just one data field in the set. Of course, it's a freeform field that my upstream SOR owns.

My plan is to carefully review each flow to identify. I appreciate your guidance. And will reply next week.

I, too, was trying the 'reverse' angle of T2C: left to right (there should be six commas), then right to left 5 numbers, fix the data in 7, and rejoin.

-john

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703