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

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.

<b>Additional troubleshooting tips</b>

• Verify whether you are experiencing a lock resource contention when using the COPY command. For more information, see How do I detect and release locks in Amazon Redshift?
• Confirm whether there is contention in your Workload Manager (WLM). You can use the STV_WLM_QUERY_STATE query to confirm whether the COPY command is trying to acquire a WLM slot.
• Check whether the COPY transaction is being rolled back. To confirm the rollback, use the SVL_STATEMENTTEXT and STL_UNDONE views.

Related information

Data sources

COPY examples

STV_WLM_QUERY_STATE