The COPY command and csv files

From: "E(dot) Matthew Finnin" <emf(dot)storage(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: The COPY command and csv files
Date: 2006-04-12 18:52:37
Message-ID: c939097c0604121152i7975b59et23b9a0b9406fde53@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

**I apologize in advance if this is a familiar question, but I couldn't
find an answer**

I am attempting to copy a csv file into a table, but no matter what I
can think of I get one of two errors. I am using Postgresql 8.1.2.

1.) Here is the first COPY command I gave:
COPY pwt61_oecd FROM stdin
WITH DELIMITER AS ','
CSV QUOTE AS ' " '
NULL AS 'na'
;

And here is the error:
ERROR: missing data for column "isocode"
CONTEXT: COPY pwt61_oecd, line 1 '/home/.../data/pwt61_oecd.csv"

The file I am attempting to copy is very large, but there are no empty
spaces that I can find, and the column "isocode" certainly has no
empty spaces or NULL values. Here are the first two lines of the
pwt61_oecd.csv file:

"country","isocode","year","POP","XRAT","PPP","cgdp","cc","ci","cg","p","pc","pg","pi","openc","cgnp","csave","y","rgdpl","rgdpch","rgdpeqa","rgdpwok","rgdptt","openk","kc","kg","ki","grgdpch"
"Australia","AUS","1950","8434.0650803","0.8931999704","0.515977685","1836.3303946","60.365553134","27.690222887","7.9941116957","57.767319935","53.853430186","73.898773273","61.642604011","47.850011457","na","31.64033517","95.021059091","9113.8072046","9173.8190347","10576.40502","22078.709149","9988.2503259","19.331539711","65.924922543","13.104642709","26.200404435","na"

(I attempted the same copy command after changing all "na" to "NULL"
and I also tried the same command after removing column headers (1st
line) from the csv file, but I received the same error message each
time.)

2.) Curious if I would get the same error, I attemtped to use the
\copy command in psql, but this time I removed the column headers from
the csv file:

\copy pwt61_oecd FROM '/home/.../data/pwt61_oecd2.csv' WITH DELIMITER
AS ',' CSV QUOTE AS ' " ' NULL AS 'na'

And the error report:
ERROR: invalid input syntax for type numeric: "na"
CONTEXT: COPY pwt61_oecd, line 1, column cgnp: "na"

Obviously I haven't used the COPY or \copy functions properly. Or I
am using the wrong data type. Could someone please point out my
mistake(s)? And on a unrelated note, in a seperate table I would like
to import there are exponential numbers (e.g. 3.77E-13). Do I need to
convert these to regular numbers if I wish to use the numeric data
type? Thank you.

-Eric

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Olinga K. Abbott 2006-04-12 21:21:32 mutually exclusive subtypes
Previous Message Alasdair Young 2006-04-12 18:15:40 Re: Indexes slower when used in decending vs. ascending order?