Thread: PGSQL 9.3.2 COPY command issues
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.
Thanks,
Kevin
spake@surewest.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
Hello, I think the in numeric column you have inserted text data.So if your column value is numeric and suppose it is 1 then use 1 instead of "1". one more thing dont use delimiter after last column value in csv file. example: postgres=# create table abc (a varchar(10),b numeric); CREATE TABLE /home/aa.csv "aaa",1 postgres=# COPY abc from '/home/aa.csv' DELIMITER ','; COPY 1 Regards, Vinayak ----- Thanks and Regards, Vinayak Pokale, NTT DATA OSS Center Pune, India -- View this message in context: http://postgresql.1045698.n5.nabble.com/PGSQL-9-3-2-COPY-command-issues-tp5811940p5811942.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
spake wrote > 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: "" Numeric columns in CSV are not quoted. What you have here is a text field in the file and a numeric field in the database. You must resolve the mis-match. The easiest way is to define the table column as text. If you ultimately need to convert the column into numeric then you should consider the import table to be a staging table and after the copy is done you migrate the copied in data to the production table while performing any data transformations needed. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/PGSQL-9-3-2-COPY-command-issues-tp5811940p5811948.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
This was exactly what I needed. Thanks so much!
On 07/18/2014 02:09 AM, Albe Laurenz wrote:
spake@surewest.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