Same CSV sometimes errors with An unescaped quote was found and missing closing Quotes

Question

I've got a large csv file (2+ GB) that when I run it  occasionally returns an error for CSVFile: A field was missing a closing quote and or An unescaped quote was found.

see csv and workflow settings a bit further down the post

the strange part is when this error happens the Alteryx error's on different lines everytime.

so 1st run:

Record #270 An unescaped quote was found.

Record #3: CSVFile: A field was missing a closing quote in record 3

2nd run:

Record #150234 An unescaped quote was found.

Record #52: CSVFile: A field was missing a closing quote in record 52

3rd run:

same thing different record number

This also happens with the closing quote

things to note about the csv file.

this file I'm getting the warnings on is static. it doesn't change from run to run

furthermore I'm using an inputtool and the settings are as follows:  AMP disabled,  (see screenshot) .

to add to this. if I open the csv file in notepad++ there are no quotes to be seen .

I would expect to see the following:

"Header1"     "Header2"     "Header3"

"Foo"     "1"     "XYZ"

"Bar"     "2"     "ABC"

Header1Header2Header3Foo1XYZBar2ABC

but got this instead (NOTE: in post there are spaces but in the file these are tabs)

Header1     Header2     Header3

Foo     1     XYZ

Bar     2     ABC

Header1Header2Header3Foo1XYZBar2ABC

Am I doing something wrong with processing this data or should I enable some settings?

danilang · Accepted Answer

Hi @MartWClimber

Open the file with 
(newline) as the delimiter, increase the Field Length to 10000 and uncheck "First row contains field names".  This should open the file with each line as a separate record and then you can start investigating.  Find any lines that do have quotes, single and double.  If these seem to be OK then you split to columns on 	 and see if you have any errors there.  Use Dynamic Rename to use the first row as field names.

About the CSV format:  Quotes are optional and are only required if you have the delimiter embedded in a field.

Also:  If the CSV file never changes store it in a different format, once you get it successfully parsed.  The Alteryx database .yxdb.  is very compact and extremely quick to read.

Good luck

Dan