Load fixed width file(Unit separator) into Redshift table using copy command

Question

I have fixed the width file with a unit separator.All records are in one row separated by a unit separator(US). This file has no header(no column names) but the header and trailer.I tired with the below options but not loading anything. Sample data.Here x1F is the unit separator.

headeryyy 000120230922x1F1 xyzzz  09102015075057    10062015102450 YY YYx1F2 xyzzz  09102015075057    10062015102450 YY YYx1FTrailerzzz

COPY table_name
FROM 's3://bucketname/sample_data.dat'
IAM_ROLE 'iam-role-arn'
DELIMITER '\031'
IGNOREHEADER 1;

Answer 1

Redshift can only use new line as the record separator AFAIK. Delimiter option specifies the field separator. If your rows are fixed width then you should use the FIXEDWIDTH option to COPY but you will need to modify your data file for the correct record separator.