Bulk Loading in Redshift, the delimiter issue

Hi,

I'm loading data to Redshift via the Bulk connection. It works fine until it encounters some records with weird characters, in this case | and \.

Writing a simple copy command with DELIMITER '\t' (tab) solves the issue, but I cant specify the delimiter in the bulk Redshift output.

Have you encountered this issue?

Thanks

Joao

Common Use Cases

Accepted answers

MichaelCh

We don't support customizing the COPY command. I am not sure what is causing the issue with \. However, | is the delimiter we currently use for our COPY command. Being able to customize the delimiter is a great idea, though; you should post it in the Idea Center.

In the meantime, you could use a MultiField formula to replace all the |s (and \s) in your string fields with some other delimiter (like \t) before running your data into the Bulk Loader.

All comments

MichaelCh

Hi Joao.

Do the records with \ in them still have the issue if you replace the \ with \\?

joaocorreia

Hi Michael,

I haven't checked. I have many columns and I dont want to add a filter for each edge case but a solution that solves everything.

The COPY command with theDELIMITER with TAB solves it but there is no DELIMITER configuration for the Redshift Bulk Output.

Joao

joaocorreia

It would be good if we can customize the COPY command bulk issues. This is my COPY command and it loads successfully, bulk doesn't.

COPY scratch.table
FROM 's3://xxxxxxxx-etl-staging/mssql/2017/'
CREDENTIALS 'aws_access_key_id=xxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxx'
GZIP
TRUNCATECOLUMNS
IGNOREHEADER AS 1
CSV QUOTE AS '"'
TIMEFORMAT AS 'YYYY-MM-DD HH:MI:SS'
ACCEPTINVCHARS AS '^'
DELIMITER '\t';

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335