Pivotal Knowledge Base

Follow

Create External Tables "Error: Escape in CSV Format Must be a Single Character"

Environment

 Product  Version
 Pivotal Greenplum  4.2.x
 OS  All Supported OS

Symptom

Creating an external table for a file with "ESCAPE" set to "OFF" produces the following error:

"Error: Escape in CSV format must be a single character"

For example, using the following syntax reproduces this issue:

CREATE EXTERNAL TABLE test(a character varying(8))
('gpfdist://localhost:8081/data/test.csv')
FORMAT 'CSV' (DELIMITER E'\t' ESCAPE 'OFF');

Cause

ESCAPE 'OFF' is only supported for TEXT format. It is not supported for CSV format.

Resolution

Replace the CSV format to TEXT.

CREATE EXTERNAL TABLE test(a character varying(8))
('gpfdist://localhost:8081/data/test.csv')
FORMAT 'TEXT' (DELIMITER E'\t' ESCAPE 'OFF');

Additional Information

ESCAPE specifies the single character that is used for C escape sequences (such as \n,\t,\100 and so on) and for escaping data characters that might otherwise be taken as row or column delimiters. The default escape character is a \ (backslash) for text-formatted files and a " (double quote) for CSV-formatted files. However, it is possible to specify another character to represent an escape. It is also possible to disable escaping in text formatted files by specifying the value 'OFF' as escape value. This is very useful for data such as text formatted web log data that has many embedded backslashes that are not intended to be escapes.

Comments

Powered by Zendesk