Import a delimited file with field values containing an escaped delimiter

Question

Given a delimited file in the following format

• fields delimited by a pipe char |
• text fields are unquoted
• some text field values contain an escaped pipe \|, an escaped line feed \\n or escaped carriage return \\r

1|text containing the pipe \| character|3
                           ^^^

what the correct combination of pgloader options might be to successfully load this file?

I tried

pgloader --type csv --field "field1,field2,field3" \
--with "fields terminated by '|'" \
--with "fields not enclosed" \
path/to/file.csv \
postgresql://user@host:port/dbname?tablename=schema.table

Which obviously didn't work for me as pgloader sees four fields instead of three.

Answer 1

You should specify escape character, cause by default it is double-quot.

Try following command:

pgloader --type csv \
         --field "field1,field2,field3" \
         --with "fields terminated by '|'" \
         --with "fields escaped by '\'" \
         path/to/file.csv \
         postgresql://user@host:port/dbname?tablename=schema.table