Redshift Copy fails due to '\' present in some of the data in my csv file

Question

I am trying to copy csv file from S3 to Redshift table. I have few of the columns present in my csv file that contains '\' in the data. Below is the sample data:  
  
columnA|columnB|columnC|columnD|columnE  
  
abc|12\|xyz|test|hello  
  
lmn|123|pqr\|test|bye  
  
jkl|124|deg|test|hi  
  
I am using below command to copy in Redhisft, but it fails:  
  
COPY redshif_schema.redshift_table_name from 's3://bucket/folder/inputfile.csv' access_key_id '' secret_access_key '' fillrecord escape delimiter as '|' IGNOREHEADER as 1 ACCEPTANYDATE emptyasnull blanksasnull maxerror 0 ;  
  
When I check raw_line in stl_load_errors table, I see below (2 back slashes):  
  
abc|12|xyz|test|hello  
  
Can someone please help with this?

Answer

Redshift is interpreting the backslash as an escape since you have specified `ESCAPE` in your `COPY`.   
  
You can try:  
* Remove `ESCAPE` if it is not really needed.  
* Specify a different escape character https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html#copy-escape  
* Use the CSV format reader https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-format.html#copy-csv

Redshift Copy fails due to '\' present in some of the data in my csv file

Relevant content