0

I have a few processes where I use the copy command to copy data from S3 into Redshift.

I have a new csv file where I am unable to figure out how I can bring in the "note" field- which is a free hand field a sales person writes anything into. It can have ";", ",", ".", spaces, new lines- anything.

Any common suggestions to copy this type of field? it is varchar(max) type in table_name.

Using this:

copy table_name
from 's3://location'
iam_role 'something'
delimiter as ','
ignoreheader 1
escape
removequotes
acceptinvchars

I get Delimiter not found

Using this:

copy table_name
from 's3://location'
iam_role 'something'
delimiter as ','
fillrecord
ignoreheader 1
escape
removequotes
acceptinvchars

I get String length exceeds DDL length

user8834780
  • 1,620
  • 3
  • 21
  • 48
  • You may have to edit the CSV file in the following way - escape the "," by replacing it with "\," and then setting the ESCAPE parameter of the COPY command to '\' (use single quotes as shown). – Victor Di Leo Aug 07 '18 at 15:52

1 Answers1

1

The second copy command command fixed your initial issue, namely of copy parsing the csv file. But now it can't be inserted because the input value exceeds the maximum column length of yr column in database. Try increasing the size of the column:

Alter column data type in Amazon Redshift

Spiff
  • 3,873
  • 4
  • 25
  • 50
  • the csv was created as an output of a SQL Server vachar(max) column and loaded into Redshift varchar(max) column. Redshift varchar(max) is 65535- would SQL server be longer than that? – user8834780 Aug 07 '18 at 15:52
  • You'd have to check the logs to find the line and field that's causing this. – Spiff Aug 07 '18 at 15:56
  • Check this: https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html – Spiff Aug 07 '18 at 15:56
  • Eh, its 146,177 chars while Redshift varchar(max) is only 65,535. Any idea how I can make column be larger? – user8834780 Aug 07 '18 at 15:59
  • Not sure if you can do that, here's a suggestion: https://forums.aws.amazon.com/thread.jspa?threadID=135143 – Spiff Aug 07 '18 at 16:03
  • 1
    My fix was to add `TRUNCATECOLUMNS` and in the raw data remove any new lines- thank you for the comment regarding source data column being longer than destination column – user8834780 Aug 07 '18 at 16:26