Re: The COPY command and csv files

Currently I have removed all double quotations from my null values
("na" has been changed to na). I have also removed the header line of
the csv file I am trying to copy into the table. I have tried various
forms of null values and everything else suggested to me, with no
luck.

Here are the table definitions. I was trying to make it as simple as possible:
emf=> \d pwt61_oecd
Table "public.pwt61_oecd"
Column | Type | Modifiers
---------+---------+-----------
country | text |
isocode | text |
year | numeric |
pop | numeric |
xrat | numeric |
ppp | numeric |
cgdp | numeric |
cc | numeric |
ci | numeric |
cg | numeric |
p | numeric |
pc | numeric |
pg | numeric |
pi | numeric |
openc | numeric |
cgnp | numeric |
csave | numeric |
y | numeric |
rgdpl | numeric |
rgdpch | numeric |
rgdpeqa | numeric |
rgdpwok | numeric |
rgdptt | numeric |
openk | numeric |
kc | numeric |
kg | numeric |
ki | numeric |
grgdpch | numeric |

This is the error I get after using the copy command:
ERROR: missing data for column "isocode"
CONTEXT: COPY pwt61_oecd, line 1:

This is the first line of the csv file, where the error is occuring:
"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

If I drop the column 'isocode' from the table and from the csv file, I
receive the following error:
ERROR: missing data for column "year"
CONTEXT: COPY pwt61_test, line 1:

Here the first line of the csv file is:
"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

Notice the error has remained in the second column and it doesn't
matter what value it holds or what the data type is.

I am at a complete loss. Thank you again for your help.

-Eric

On 4/14/06, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
> E. Matthew Finnin wrote:
> > Thank you both for replying. When I wrote ' " ' I meant '"', I was
> > trying to make it easier to read for an email, but all I did was
> > confuse things. Anyway, I tried dropping the quote and delimiter
> > comments from the command, but I still received a missing data error
> > on line 1 of the csv file. The data I'm using is all OECD countries
> > selected from the Penn World Tables 6.1 website
> > (http://pwt.econ.upenn.edu/php_site/pwt61_form.php) and cutting and
> > pasting the output into a csv file.
> >
> > Bruce you mentioned there is a cvs backpatch for version 8.1.x. Maybe
> > this is my problem. How do I check if this is installed and, assuming
> > it isn't, how do I go about installing it? I've come across scripts
> > in the mail lists related to something like this, but I wasn't sure if
> > they were final or even for anyone other than developers. If its of
> > any interest to anyone, I installed Postgresql 8.1.x as an Kubuntu
> > package.
>
> OK, got it. If you remove the first line, does it work? Also, those
> "n/a" are not going to work because we don't allow the NULL indicator to
> be in double quotes. Try change "n/a" to n/a and see if that helps.
> Also try removing the first line to see if that helps. Also, show use
> the table definition you are trying to load into, and the error message,
> and a line generating an error.
>
> I don't think you need anything backpatched.
>
> --
> Bruce Momjian http://candle.pha.pa.us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>