ERROR: Load into table - Invalid data in COPY command

Question

I am importing data from CSV file into Amazon Redshift using COPY command and getting error :

COPY table1 FROM 's3://cucket1/table1.csv' 
credentials 'aws_access_key_id=aaaa;aws_secret_access_key=aaaa' 
IGNOREHEADER 1  
csv 
NULL AS '\N' 
timeformat 'auto';

ERROR: Load into table 'table1' failed. Check 'stl_load_errors' system table for details.

my csv file looks like :

"id","name","created_at","updated_at"
"1","2","NULL","NULL"
"2","1","NULL","NULL"
"6","1","NULL","NULL"
"87","1","NULL","NULL"

When i remove NULL values from csv file, COPY command working fine. How to handle NULL values in redshift COPY command?enter code here

botchniaque botchniaque · Accepted Answer · 2019-04-16T13:09:46

Looks like your NULLs have format "NULL". This should work:

COPY table1 FROM 's3://cucket1/table1.csv' 
credentials 'aws_access_key_id=aaaa;aws_secret_access_key=aaaa' 
IGNOREHEADER 1  
csv 
NULL AS 'NULL' 
timeformat 'auto';

Also having a look into the stl_load_errors will give you a better idea on what's wrong.

Have you tried select * from stl_load_errors order by starttime desc limit 100;? This query should give you all the details about the data that could not be loaded. I suspect that in your case it was that "NULL" coundn't have been parsed intoTIMESTAMP type.

ERROR: Load into table - Invalid data in COPY command

1 Answers