Load delimited files with unquoted strings that contain escaped delimiters

[pmatseykanets]

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.

Any insights would be highly appreciated.

[dimitri]

From the docs, try the fields escaped by '\' option:

• fields escaped by

  Takes either the special value backslash-quote or double-quote, or any value supported by the fields terminated by option (see below). This value is used to recognize escaped field separators when they are to be found within the data fields themselves. Defaults to double-quote.

[pmatseykanets]

Thank you so much for getting back to me. Unfortunately this option doesn't work for me with this files or I'm doing something wrong.

Here is the content of a sample file test.txt

1|some \| pipe|3
4|line \
feed|5

The first line contains the escaped delimiter. The second one contains the escaped linefeed.

Here is the table definition

CREATE TABLE test(f1 INT, f2 TEXT, f3 INT);

Trying to load with

pgloader --on-error-stop --type csv --field "f1,f2,f3" \
--with "fields terminated by '|'" \
--with "fields not enclosed" \
--with "fields escaped by backslash-quote" \
test.txt \
postgresql://user@host:port/dbname?tablename=test

Here is what I get back

2017-10-05T03:43:38.234000+01:00 ERROR PostgreSQL ["test"] Database error 22P02: invalid input syntax for integer: "pipe"
CONTEXT: COPY test, line 1, column f3: "pipe"
2017-10-05T03:43:38.237000+01:00 ERROR PostgreSQL ["test"] Database error 22P02: invalid input syntax for integer: "feed"
CONTEXT: COPY test, line 2, column f1: "feed"
2017-10-05T03:43:38.441000+01:00 LOG report summary reset
             table name       read   imported     errors      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0          0          0.006s
-----------------------  ---------  ---------  ---------  --------------
                   test          3          1          2          0.056s
-----------------------  ---------  ---------  ---------  --------------
        Files Processed          1          1          0          0.015s
COPY Threads Completion          2          2          0          0.083s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          3          1          2          0.445s

And the contents of the table

# select * from test;
 f1 |   f2   | f3
----+--------+----
  4 | line \ |  ¤
(1 row)

[dimitri]

Here's a self-contained test case to reproduce your example:

load csv
  from inline (f1, f2, f3)
  into pgsql:///pgloader
  target table t640

  with fields terminated by '|',
       fields not enclosed,
       fields escaped by '\',
       csv escape mode following

  before load do
    $$ drop table if exists t640; $$,
    $$ CREATE TABLE t640(f1 INT, f2 TEXT, f3 INT); $$;


1|some \| pipe|3
4|line \
feed|5

I'm not sure it's possible to escape a Newline, that said, so I still have the failure you're mentioning.