Redshift - Delimited value missing end quote

Question

Im trying to load a CSV file to redshift.

Delimiter '|'

1'st column of CSV:

1 |Bhuvi|"This is ok"|xyz@domain.com

I used this command to load.

copy tbl from 's3://datawarehouse/source.csv'   
iam_role  'arn:aws:iam:::role/xxx'cas-pulse-redshift' 
delimiter '|' 
removequotes 
ACCEPTINVCHARS ;

ERROR:

raw_field_value | This is ok" |xyz@domain.com
err_code        | 1214
err_reason      | Delimited value missing end quote

then I tried this too.

copy tbl from 's3://datawarehouse/source.csv'   
iam_role  'arn:aws:iam:::role/xxx' 
CSV QUOTE '\"' 
DELIMITER '|'   
ACCEPTINVCHARS ;

user_2130670 · Answer

Disclaimer - Even though this post does not answer the question asked here, I am posting this analysis in case it helps some one.

The error "Delimited value missing end quote" can be reported in cases where a quoted text column is missing the end quote, or if the text column value has a new line in the value itself. In my case, there was a newline in the text column value.

As per RFC 4180 the specification of CSV says,

Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.
For example:

    "aaa","b CRLF
    bb","ccc" CRLF
    zzz,yyy,xxx

So a valid CSV can have multi-line rows, and the correct way to import it in Redshift is to specify the CSV format option. This also assumes that all columns having the quote character in the value will have the quote character escaped by another preceding quote character. This is also as per the CSV RFC specification.

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.
For example:

   "aaa","b""bb","ccc"

If the file that we are trying to import is not a valid CSV, and is just named as a .CSV file as the case may just be, then we have the following options.

Try copying the file without specifying the CSV option, and fine tuning the delimiter and escape and quoting behaviour with the corresponding copy options.
If a set of options is not able to consistently copy data, then pre-process the file to make it consistent.

In general, it helps to make the behaviour deterministic if we try to export and import data in formats that are consistent.

Redshift - Delimited value missing end quote

1 Answers