CODEWITHSUNDEEP
copydelimiteramazon-redshift
bashan
bashan

Reputation: 3602

Uploading data to RedShift using COPY

I am trying to upload data to RedShift using COPY command.

On this row: 4072462|10013868|default|2015-10-14 21:23:18.0|0|'A=0

I am getting this error: Delimited value missing end quote

This is the COPY command: copy test from 's3://test/test.gz' credentials 'aws_access_key_id=xxx;aws_secret_access_key=xxx' removequotes escape gzip

Upvotes: 2

Views: 3900

Answers (1)

ketan vijayvargiya
ketan vijayvargiya

Reputation: 5649

First, I hope you know why you are getting the mentioned error: You have a a single quote in one of the column values. While using the removequotes option, Redshift documentation clearly says that:

If a string has a beginning single or double quotation mark but no corresponding ending mark, the COPY command fails to load that row and returns an error.

One thing is certain: removequotes is certainly not what you are looking for.

Second, so what are your options?

  1. If preprocessing the S3 file is in your control, consider using the escape option. Per the documentation,

When this parameter is specified, the backslash character (\) in input data is treated as an escape character.

So your input row in S3 should change to something like:

4072462|10013868|default|2015-10-14 21:23:18.0|0|\'A=0

  1. See if the CSV DELIMITER '|' works for you. Check documentation here.

Upvotes: 4

Related Questions