From: | spake(at)surewest(dot)net |
---|---|
To: | Pgsql novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: PGSQL 9.3.2 COPY command issues |
Date: | 2014-07-20 03:49:36 |
Message-ID: | 4fe9b6bf514847c400b6ed4496228e8f@surewest.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
This was exactly what I needed. Thanks so much!
On 07/18/2014 02:09 AM, Albe Laurenz wrote:
> spake(at)surewest(dot)netwrote:
>
>> 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 | Paul Linehan | 2014-07-20 10:04:21 | Help with a JOIN. |
Previous Message | Gerald Cheves | 2014-07-20 03:09:24 | Re: JBDC LDAP support |