Invalid quote formatting for CSV with double quotes (Redshift loading)

2
votes

I have a CSV containing the following:

id,homie_id,user_id,some_data,some_datetime,list_stuff,confirmed_at,report_id
     1,57,1,,,"{\"assets\":[]}","2014-12-26 16:50:32",18
     2,59,1,,,"{\"assets\":[]}","2014-12-26 16:50:46",18

When I run the COPY command, I get an error "Invalid quote formatting for CSV"

Why is that? It has the backslash before the quote, so it should be acceptable. I see Redshift says to use "" instead (https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-format.html#copy-data-format-parameters) but is there a way to tell it to accept \" since that is a valid way to escape quotes with CSVs?

I've already tried Googling and don't see a reason this wouldn't work.

1
csvamazon-redshift

1 Answers

5
votes

There isn't really a way to force Redshift to use backslash as the escape character. You have to convert your input data to the format that Redshift can handle. One way is just to replace all backslahes with double quotes. For example, "{\"assets\":[]}" turns into "{""assets"":[]}" which is then parsable by Redshift and in the end the actual data should look like {"assets":[]} for that field.

From the docs:

The default quote character is a double quotation mark ( " ). When the quote character is used within a field, escape the character with an additional quote character. For example, if the quote character is a double quotation mark, to insert the string A "quoted" word the input file should include the string "A ""quoted"" word"