Escaping in CSV Formatted Files
By default, the escape character is a " (double quote) for CSV-formatted files. If you want to use a different escape character, use the ESCAPE clause of COPY, CREATE EXTERNAL TABLE or gpload to declare a different escape character. In cases where your selected escape character is present in your data, you can use it to escape itself.
For example, suppose you have a table with three columns and you want to load the following three fields:
- Free trip to A,B
- 5.89
- Special rate "1.79"
Your designated delimiter character is , (comma), and your designated escape character is " (double quote). The formatted row in your data file looks like this:
"Free trip to A,B","5.89","Special rate ""1.79"""
The data value with a comma character that is part of the data is enclosed in double quotes. The double quotes that are part of the data are escaped with a double quote even though the field value is enclosed in double quotes.
Embedding the entire field inside a set of double quotes guarantees preservation of leading and trailing whitespace characters:
"Free trip to A,B ","5.89 ","Special rate ""1.79"" "