Re: The COPY command and csv files - Mailing list pgsql-novice

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@candle.pha.pa.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. +
>