Search code examples
postgresqlcsvpgloader

How to import empty strings as null values from CSV file - using pgloader?

I am using pgloader to import from a .csv file which has empty strings in double quotes. A sample line is

12334,0,"MAIL","CA","","Sanfransisco","TX","","",""

After a successful import, the fields that has double quotes ("") are shown as two single quotes('') in postgres database.

Is there a way we can insert a null or even empty string in place of two single quotes('')?

I am using the arguments - 

WITH truncate,
           fields optionally enclosed by '"',
           fields escaped by double-quote,
           fields terminated by ','
SET client_encoding to 'UTF-8',
           work_mem to '12MB',
           standard_conforming_strings to 'on'

I tried using 'empty-string-to-null' mentioned in the documentation like this - 

CAST column enumerate.fax using empty-string-to-null

But it gives me an error saying -

pgloader nph_opr_addr.test.load An unhandled error condition has been signalled: At LOAD CSV

^ (Line 1, Column 0, Position 0) Could not parse subexpression ";" when parsing

Solution

  • Use the field option:

    null if blanks

    Something like this:

    ...
having fields foo, bar, mynullcol null if blanks, baz

    From the documentation:

    null if

    This option takes an argument which is either the keyword blanks or a double-quoted string.

    When blanks is used and the field value that is read contains only space characters, then it's automatically converted to an SQL NULL value.

    When a double-quoted string is used and that string is read as the field value, then the field value is automatically converted to an SQL NULL value