How to stop getting error and keep data: Missing newline: Unexpected character 0x31 found at location 116

Question

I'm trying to copy data from Amazon s3 into Amazon Redshift but I keep getting the "Unexpected character" newline error at the same line every time. Its a random line in the middle of the file and this has been happening to a lot of my files that I'm trying to copy over.

Here is the copy statement:

COPY sample_table FROM "S3://bucket/file.psv' iam_role 'role' delimiter '|'
ACCEPTINVCHARS IGNOREBLANKLINES DATEFORMAT 'auto' TIMEFORMAT 'auto'
emptyasnull blanksasnull maxerror as 100000 ;

Here is the table format:

create table sample_table (
id varchar (25),
model varchar (25),
api varchar (25),
msgdatetime timestamp,
msgdate date,
alias bigint,
key varchar (255),
value varchar (50)
) ;

Here is the raw line from the data:

AAA111111111|AA1111111|sample_API|2019-06-06 11:20:00|2019-06-06|111111111|Sample_key|1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

It should also be noted that this is part of an automated process where the sql above is used to copy numerous files that are all causing the same problem.

Answer 1

you have more columns in your data than you have in your table

id = AAA111111111
model = AA1111111
api = sample_API
msgdatetime = 2019-06-06 11:20:00
msgdate  = 2019-06-06
key = 111111111
value = Sample_key

which leaves "1" at the end as the next column. 1 is 0x31

You probably need a new column (that I have called newcol below)

create table sample_table (
id varchar (25),
model varchar (25),
api varchar (25),
msgdatetime timestamp,
msgdate date,
newcol varchar(255),
key varchar (255),
value varchar (50)
) ;