Redshift COPY command with "^A" delimiter

Question

I am trying to use a control A ("^A") delimited file to load into redshift using COPY command, I see default delimiter is pipe (|) and with CSV it is comma.

I couldnt file a way to use ^A, when i tried COPY command with ^A or \x01, it is throwing below message. Anybody tried this before? documentation says we can use delimiter, but no clue on using ^A.

Password: ERROR: COPY delimiter must be a single character

Answer 1

I have used '\\001' as a delimiter for ctrl+A based field separation in redshift and also in Pig.

Example :

copy redshiftinfo from 's3://mybucket/data/redshiftinfo.txt' 
credentials 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>' 
delimiter '\\001'