When I try to load a CSV file into Amazon Redshift using COPY, nothing is loaded

Last updated: 2020-09-18

I'm trying to load a CSV file from Amazon Simple Storage Service (Amazon S3) into Amazon Redshift using the COPY command. However, nothing is loaded and no errors are returned, even though the file contains records. How do I resolve this?

Resolution

COPY fails to load data to Amazon Redshift if the CSV file uses carriage returns ("\\r", "^M", or "0x0D" in hexadecimal) as a line terminator. Because Amazon Redshift doesn't recognize carriage returns as line terminators, the file is parsed as one line. When the COPY command has the IGNOREHEADER parameter set to a non-zero number, Amazon Redshift skips the first line, and therefore, the entire file. No load errors are returned because the operation is technically successful.

To resolve this problem, replace carriage returns with CRLF ("\\r\\n" or "0x0D0A" in hexadecimal) or LF (“\\n” or “0x0A” in hexadecimal). Upload the modified file to the S3 bucket, and then try the COPY command again.

Additional troubleshooting tips


Did this article help?


Do you need billing or technical support?