Redshift Error 1202 "Extra column(s) found" using COPY command

user_3821822 0
Viewed 15556

I'm getting a 1202 Extra column(s) found error in Redshift when trying to load a simple CSV. I've made sure that there are no additional columns nor any unescaped characters in the file that would cause the COPY command to fail with this error.

Here's the created target table:

create table test_table(
  name varchar(500),
  email varchar(500),
  developer_id integer,
  developer_name varchar(500),
  country varchar(20),
  devdatabase varchar(50));

I'm using a simple CSV with no header and only 3 rows of data:

john smith,john@gmail.com,123,johndev,US,comet
jane smith,jane@gmail.com,124,janedev,GB,titan
jack smith,jack@gmail.com,125,jackdev,US,comet

Unfortunately my COPY command fails with err_1202 "Extra column(s) found".

COPY test_table 
FROM 's3://mybucket/test/test_contacts.csv'    
WITH credentials AS 'aws_access_key_id=<awskey>;aws_secret_access_key=<mykey>'
CSV;

There are no additional columns in the file.

amazon-redshift amazon-web-services csv amazon-s3
5 Answers

I was also facing the same issue while loading the data. i rectified using following codes :

copy yourtablename
from 'your S3 Locations'
credentials 'your AWS credentials' 
delimiter ',' IGNOREHEADER 1 
removequotes
emptyasnull
blanksasnull
maxerror 5;
user_50685470
user_95187340

Make sure the correct delimiter is specified in the copy statement (and the source files). I run into the same issue. After a couple of attempts with different delimiters (while unloading table to s3 files, then copying into another table from the s3 files), I was able to solve the issue by using the delimiter '\t'. Here is the full example in my case:

copy <TABLE-NAME>
from 's3://<FILES/LOCATION>'
access_key_id '<INSERT>'
secret_access_key '<INSERT>'
delimiter '\t'
ignoreheader 1
maxerror 10;
user_28397860

notice glue is not as robust as one might think, column order plays a major role, check your table order as well as the table input, make sure the order and data types are identical, also see AWS Glue Developer Guide for more info

in addition, make sure you disabled 'Job bookmark' in the 'Job details' tab, for any development or generic job this is a major source of headache and troubles

user_13084350

For me, it turned out to be that I executed the scripts on the wrong database within the cluster.

user_106963560
Related