When I try to load a CSV file into Amazon Redshift using COPY, nothing is loaded
Last updated: 2020-01-03
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.
This problem happens when 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 was 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.