CODEWITHSUNDEEP
pythonamazon-s3amazon-redshiftetl
DataEngineer
DataEngineer

Reputation: 31

Getting "invalid quote formatting for csv" error while copying data from s3 to redshift

My csv file uploaded to S3 is having data as below:

id,name,post,description,salary
10001,ajay,jr.Engg,"to handle ""cloud related tasks",100000
10002,bimal,sr.engg,"to handle \\db tasks
hello_to_all",200000
10003,chinmay,sr.support,"for deployment """@related activities",250000
10004,dipak,HR,"Resource mngt,Control",300000
10005,Emili,jr.HR,Resource mngt|Control,350000
10006,Falguni,HR,Resource mngt    control,400000

I am using below command to copy this data to redshift:

copy {table_name}
from 's3_location'
credentials 'aws_access_key_id={access_key};aws_secret_access_key={secret_access_key}'
csv delimiter ',' quote as '\"' fillrecord blanksasnull IGNOREBLANKLINES emptyasnull acceptinvchars '?' ignoreheader as 1  compupdate off statupdate off truncatecolumns region '{region_name}'

I am getting "invalid quote formatting for csv" error for this command but when I use below command

copy {table_name}
from 's3_location'
credentials 'aws_access_key_id={access_key};aws_secret_access_key={secret_access_key}'
csv delimiter ',' quote as '^' fillrecord blanksasnull IGNOREBLANKLINES emptyasnull acceptinvchars '?' ignoreheader as 1  compupdate off statupdate off truncatecolumns region '{region_name}'

the error changes and the comma (,) present in between the text value for description column gets considered as delimiter and throws error as "Invalid digit, Value 'C', Pos 0, Type: Integer".

Please suggest the correct copy option which will able to copy data from s3 location to redshift.

Upvotes: 3

Views: 2832

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

First off look at stl_load_errors for the copy in question. This will give the line and the field that has the error.

I suspect the that quoting error is on line starting with 10003. You have triple double quotes which isn't valid. Redshift follows rtf 4180 spec for CSV files - https://datatracker.ietf.org/doc/html/rfc4180 and to achieve a double quote inside a quoted value requires 2 double quotes - Rule 6.

Upvotes: 2

Related Questions