CODEWITHSUNDEEP
amazon-s3amazon-redshift
Pradeep Chintapalli
Pradeep Chintapalli

Reputation: 155

How to Copy data from s3 to Redshift with "," in the field values

I am faced with "Extra column(s) found" error while reading the data from S3 to Redshift. Since my data has 863830 rows an 21 columns, ill give you a small example of how the data is. 

create table test_table(
name varchar(500),
age varchar(500)
)

and my data would be

(ABC,12)
(First,Last,25)

where First,last should go into a single columns

Unfortunately, i am unable to do that with this copy command 

COPY test_table from 'path'
iam_role 'credentials'
region 'us-east-1'
IGNOREHEADER 1
delimiter as ','

Is there any way to accomodate commas into a field ?

Upvotes: 0

Views: 1891

Answers (2)

Bill Weiner
Bill Weiner

Reputation: 11032

Your data doesn't conform to the CSV specification. See RTF-4180

To store your example data the field with the comma in it needs to be enclosed in " "

ABC,12
"First,Last",25

The parentheses in the data file will also need to be removed as these will be interpreted as part of the data fields.

Alternatively you could change the delimiter of your data from "," to something else like "%". However if this character is in your data then you are right back where you started. Ad hoc delimited files only work if you use a character that will never be in your data. This is why I recommend that you use the more robust CSV specification and use the "CSV" option to COPY.

Upvotes: 1

Tushar Gupta
Tushar Gupta

Reputation: 251

Is it a CSV file that you're trying to load? If so, try loading with CSV format parameter specified in the command, rather than using delimiter ',' parameter. Here's an example -

COPY test_table from 'path' iam_role 'credentials' region 'us-east-1' IGNOREHEADER 1 CSV;

If that doesn't help, you may have to use the ESCAPE parameter. This would need modifications in your file too. Here's an example - https://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_command_examples-copy-data-with-the-escape-option

Upvotes: 3

Related Questions