Why isn't the COPY command loading my .csv file into Amazon Redshift?

Resolution

If the .csv file uses carriage returns as a line terminator, then COPY fails to load data to Amazon Redshift. Carriage returns include "\\r", "^M", or "0x0D" in hexadecimal.

Because Amazon Redshift doesn't recognize carriage returns as line terminators, Amazon Redshift parses the file as one line. When you set the IGNOREHEADER parameter to a non-zero number on the COPY command, Amazon Redshift skips the first line. As a result, Amazon Redshift also skips the entire file. The command doesn't return load errors because the operation is technically successful.

To resolve this issue, 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 run the COPY command again.

If you replace the carriage returns and still experience issues, then take the following actions:

• Check whether you experience a lock resource contention when you run the COPY command. For more information, see How do I detect and release locks in Amazon Redshift?
• Use the STV_WLM_QUERY_STATE query to check whether the COPY command tries to acquire a Workload Manager (WLM) slot.
• Use the SVL_STATEMENTTEXT and STL_UNDONE views to check whether the COPY command rolled back the copy transaction.

Related information

Data sources

COPY examples