From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "spake(at)surewest(dot)net *EXTERN*" <spake(at)surewest(dot)net>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: PGSQL 9.3.2 COPY command issues |
Date: | 2014-07-18 09:09:02 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17D1CFB7@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
spake(at)surewest(dot)net wrote:
> Hi there. My first post to this list. Hopefully, as time goes by, I will be able to answer more
> questions than I ask.
>
> I'm trying to import a very large CSV file of npi data into psql 9.3.2 using the COPY command. The
> data in the CSV file is surrounded by quotes. Although there is a header, the data looks like this:
> "1679576722","1","","","","WIEBE", etc.
>
> Here is my psql command:
> COPY data FROM '/Developer/data_20140608.csv' DELIMITER ',' CSV HEADER;
>
> and here is the error I get:
> ERROR: invalid input syntax for type numeric: ""
> CONTEXT: COPY npi_data, line 2, column replacement_npi: ""
>
> psql seems to be choking on the empty fields that are quoted. Any suggestions as to how I get around
> this? I've tried including the QUOTES statement but it made no difference.
The problem is that the empty string is not a valid integer value.
It might be tempting to tell COPY that it should consider that as a NULL value:
COPY tabname FROM 'filename' (FORMAT 'csv', NULL '""');
but that results in
ERROR: CSV quote character must not appear in the NULL specification
So it looks like you'll have to preprocess these files, e.g. with
sed -e 's/""/(null)/g'
and then you could import with
COPY tabname FROM 'filename' (FORMAT 'csv', NULL '(null)');
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Vinayak Pokale | 2014-07-18 09:27:37 | Re: PGSQL 9.3.2 COPY command issues |
Previous Message | spake | 2014-07-17 19:01:26 | PGSQL 9.3.2 COPY command issues |