Amazon Redshift - Copy from Csv - Single Double Quote in Row - Invalid Quote Formatting for Csv Error
I'm Loading a Csv File from S3 into Redshift. This Csv File Is Analytics Data Which Contains the Pageurl (Which May Contain User Search Info Inside a Query...
I'm loading a CSV file from S3 into Redshift. This CSV file is analytics data which contains the PageUrl (which may contain user search info inside a query string for example).
It chokes on rows where there is a single, double-quote character, for example if there is a page for a 14" toy then the PageUrl would contain:
"-toy/1234.html
Redshift understandably can't handle this as it is expecting a closing double quote character.
The way I see it my options are:
- Pre-process the input and remove these characters
- Configure the COPY command in Redshift to ignore these characters but still load the row
- Set MAXERRORS to a high value and sweep up the errors using a separate process
Option 2 would be ideal, but I can't find it!
Any other suggestions if I'm just not looking hard enough?
Thanks
Duncan
3 Answers
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:
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:
I have done this using ---> DELIMITER ',' IGNOREHEADER 1; at the replacement for 'CSV' at the end of COPY command. Its working really fine.
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
