Re: The COPY command and csv files

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
Fhren durch przise Daten

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