Amazon Redshift - COPY from CSV - single Double Quote in row - Invalid quote formatting for CSV Error
It's 2017 and I run into the same problem, happy to report there is now a way to get redshift to load csv files with the odd " in the data.
The trick is to use the ESCAPE keyword, and also to NOT use the CSV keyword. I don't know why, but having the CSV and ESCAPE keywords together in a copy command resulted in failure with the error message "CSV is not compatible with ESCAPE;" However with no change to the loaded data I was able to successfully load once I removed the CSV keyword from the COPY command.
You can also refer to this documentation for help: http://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html#copy-escape
Unfortunately, there is no way to fix this. You will need to pre-process the file before loading it into Amazon Redshift.
The closest options you have are CSV [ QUOTE [AS] 'quote_character' ]
to wrap fields in an alternative quote character, and ESCAPE
if the quote character is preceded by a slash. Alas, both require the file to be in a particular format before loading.
See:
- Redshift COPY Data Conversion Parameters
- Redshift COPY Data Format Parameters