Re: Using COPY

Hi,

I recently copied a foxpro table into postgres. The first step I did was
have foxpro create a comma delimited file. I tried to us pgAdminII emport
wizard to bring in the foxpro comma delimited file, but I keep getting
error, so pgAdmin II or postgres would always rollback everything on me. I
then used the home site editor which can be set to either pc or unix formats
in the options. I set it to unix and opened the comma delimited file with
it and then immediately save it, thus putting it into the unix end of line
format. Then I was able to use the postgres copy command to bring all the
text entries into a postgres database. When I browsed the database however,
the field had the double quote in them and some fields did not delimite
correctly. I discovered that postgres does not like the text fields to be
double quoted. I tried to strip the qoutes out of the text delimited file,
but home site couldn't handle it and keep locking up on me. The text
delimited file had around 17,000 records. I copies the file over to linux
and then used the x-Emacs editor to replace all double quotes with an empty
replace field. This took some time be x-Emacs accomplished the task. I
then used the postgres copy commnad again and everything converted over
corrected.

----- Original Message -----
From: "tony" <tony(at)animaproductions(dot)com>
To: <joe(at)jwebmedia(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, May 14, 2002 9:30 AM
Subject: Re: [GENERAL] Using COPY

> On Tue, 2002-05-14 at 16:15, Joseph Koenig wrote:
> > We're converting a db from Access to PostgreSQL for a client - I need to
> > export all of his data into postgres on Linux - I'm trying to use the
> > COPY command, however, his comment fields have line breaks in them which
> > causes the copy to throw a parse error at that line. I'm sure others
> > have run into this - is there an easy way to avoid this? Thanks,
>
> Welcome to pain! Now when someone asks me to port data, I bill each and
> every hour spent - no more estimates on how much it will cost!!!
>
> I'm sure someone will recommend a perl script to rip them out.
>
> If you aren't a perl guru then run out and find a Mac with BBEdit which
> is the absolute best text editor in the world. A combination of BBEdit
> and OpenOffice will clean up and format any text file and save it to
> many types of text including CSV.
>
> Cheers
>
> Tony Grant
> --
> RedHat Linux on Sony Vaio C1XD/S
> http://www.animaproductions.com/linux2.html
> Macromedia UltraDev with PostgreSQL
> http://www.animaproductions.com/ultra.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly