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

Hello Matthew,

I've noticed that the numeric fields are quoted with ".
Try it after you removed the " from the numeric fields.

Is it now working?

With kind regards

Florian Reiser

--
http://www.ra-bc.de
RA Unternehmensberatung
F�hren durch pr�zise Daten

""E. Matthew Finnin"" <emf.storage@gmail.com> schrieb im Newsbeitrag
news:c939097c0604180006r5bead672t199985c7b8858f71@mail.gmail.com...
> 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. +
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>