2

I am trying to load the data that includes a new line within a field:

001|myname|fav\
movie | myaddress| myphone|

There is a blank line between fav\movie.

I am loading the data with this command:

COPY catdemo
FROM 's3://tickit/catego.csv'
IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'
REGION 'ap-south-1'
DELIMITER '|'
ESCAPE
ACCEPTINVCHARS
IGNOREBLANKLINES
NULL AS '\0';

I want to ignore this blank line, can anyone help me?

its showing delimiter not found between fav\ and movie, but its actually a single line.

fav\

movie

Nirmal Prabhu
  • 23
  • 1
  • 5
  • What is the copy command you are trying to use? – hadooper Nov 22 '17 at 13:55
  • copy catdemo from 's3://tickit/catego.csv' iam_role 'arn:aws:iam:::role/' region 'us-west-2' removequotes delimiter "|" escape; – Nirmal Prabhu Nov 22 '17 at 14:00
  • you can use the option `IGNOREBLANKLINES` see this document for reference. http://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html#copy-ignoreblanklines – Usman Azhar Nov 22 '17 at 15:20
  • I tried this, but still its not working. copy catdemo from 's3://tickit/catego.csv' iam_role 'arn:aws:iam:::role/' region 'ap-south-1' delimiter '|' ESCAPE ACCEPTINVCHARS IGNOREBLANKLINES null as '\0'; – Nirmal Prabhu Nov 22 '17 at 15:31
  • where is the blank line anyway? i cannot see a blank line in your example. i see a line split into 2 lines. – Jon Scott Nov 22 '17 at 16:36
  • 1
    is your copy failing with any error, can you check system load error tables in redshift. ```select filename,colname,type,col_length,raw_field_value,err_reason,err_code from stl_load_errors where filename like '%filename%' ``` – Usman Azhar Nov 22 '17 at 17:15
  • Its showing delimiter not found, – Nirmal Prabhu Nov 23 '17 at 07:35
  • Does your data contain null terminated C strings? The way you are specifying your NULL makes me suspect that. Also, would it be possible to quote fields that have newlines? Then you could use the CSV option instead of ESCAPE to solve the problem. Also, if there is a blank line between the words doesn't that mean there are two newline characters there and you would need a backslash before both? – systemjack Nov 26 '17 at 08:25

0 Answers0