Thread: Using COPY

Using COPY

From
Joseph Koenig
Date:
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,

Joe

Re: Using COPY

From
"Bertin, Philippe"
Date:
Hi, Joe,

I'd say : make an ODBC- connection to the PostgreSQL database from within
Access. Then, import the (local) Access table's data into the linked Access
table. I think this should do (provided you don't have some other exotic
conversion needs). I wouldn't convert the Access data into text format and
import these into PostgreSQL?

Regards,

Philippe Bertin.

> -----Original Message-----
> From:    Joseph Koenig [SMTP:joe@jwebmedia.com]
> Sent:    dinsdag 14 mei 2002 16:15
> To:    pgsql-general@postgresql.org
> Subject:    [GENERAL] Using COPY
>
> 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,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Using COPY

From
tony
Date:
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


Re: Using COPY

From
Tom Lane
Date:
Joseph Koenig <joe@jwebmedia.com> writes:
> 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,

You'll need to transform the linebreaks that should be data into \n
(backslash-n) or \LF (backslash-newline) sequences.

            regards, tom lane

Re: Using COPY

From
"Bertin, Philippe"
Date:
Tony,

Wouldn't the 'simple' way I thought of (i.e., using ODBC and Access
combined) do the trick ?

Regards,

Philippe Bertin.

> -----Original Message-----
> From:    tony [SMTP:tony@animaproductions.com]
> Sent:    dinsdag 14 mei 2002 16:30
> To:    joe@jwebmedia.com
> Cc:    pgsql-general@postgresql.org
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: Using COPY

From
tony
Date:
On Tue, 2002-05-14 at 17:16, Bertin, Philippe wrote:

> Wouldn't the 'simple' way I thought of (i.e., using ODBC and Access
> combined) do the trick ?

I had heard of the other way around so often (Access as front end to
Postgres) that my head was all warped!!!

Yes this is a much better way. I had to do it the tough way because the
RDBMS didn't have the ODBC mudule installed. There was no CD-Rom, no
doc...

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


Re: Using COPY

From
"Joel Burton"
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Tuesday, May 14, 2002 11:04 AM
> To: joe@jwebmedia.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using COPY
>
>
> Joseph Koenig <joe@jwebmedia.com> writes:
> > 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,
>
> You'll need to transform the linebreaks that should be data into \n
> (backslash-n) or \LF (backslash-newline) sequences.

This would be the best way. It arrives perfectly in PG.

Or, if that's tricky for you to do w/the conversion/output tools you have at
your disposal, turn the linebreaks into some unusual marker ("@@~@@") using
any tool (emacs, perl, even Microsoft Word will do), pull the data in using
COPY, and once in pgsql, you can fix this with a query.

If you're going to use Access as a front-end onto the data, there's a
document with some help for this setup at
http://joelburton.com/resources/pgaccess.

HTH.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: Using COPY

From
"Leland F. Jackson, CPA"
Date:
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@animaproductions.com>
To: <joe@jwebmedia.com>
Cc: <pgsql-general@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly


Re: Using COPY

From
"Peter Darley"
Date:
Joel,
    Just out of curiosity, how would that query to update the marker be
written?  It's very similar to something I'd like to do, but have been
unable to work out.
Thanks,
Peter Darley

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Joel Burton
Sent: Tuesday, May 14, 2002 9:25 AM
To: Tom Lane; joe@jwebmedia.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using COPY

Or, if that's tricky for you to do w/the conversion/output tools you have at
your disposal, turn the linebreaks into some unusual marker ("@@~@@") using
any tool (emacs, perl, even Microsoft Word will do), pull the data in using
COPY, and once in pgsql, you can fix this with a query.




Re: Using COPY

From
"Joel Burton"
Date:
> -----Original Message-----
> From: Peter Darley [mailto:pdarley@kinesis-cem.com]
> Sent: Tuesday, May 14, 2002 3:46 PM
> To: Joel Burton
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Using COPY
>
>
> Joel,
>     Just out of curiosity, how would that query to update the marker be
> written?  It's very similar to something I'd like to do, but have been
> unable to work out.
> Thanks,
> Peter Darley

create function fix_marker(text) returns text as '
  $_ = shift;
  s/@@~@@/\\n/;
  return $_;
' language 'plperl';

update tblFoo set foo=fix_marker(foo) where foo like '%@@~@@%';

would do nicely. Or you could do it in plpython, plruby (if installed), or
plperl.

You could do it in plpgsql with a combination of ugly left || right with
strpos, but it's a lot easier in languages that have string-replace
functions.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Joel Burton
> Sent: Tuesday, May 14, 2002 9:25 AM
> To: Tom Lane; joe@jwebmedia.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using COPY
>
> Or, if that's tricky for you to do w/the conversion/output tools
> you have at
> your disposal, turn the linebreaks into some unusual marker
> ("@@~@@") using
> any tool (emacs, perl, even Microsoft Word will do), pull the
> data in using
> COPY, and once in pgsql, you can fix this with a query.
>
>
>


Re: Using COPY

From
"Peter Darley"
Date:
Joel,
    Darn, I was hoping it was possible to do it with just SQL. :)
Thanks,
Peter Darley

-----Original Message-----
From: Joel Burton [mailto:joel@joelburton.com]
Sent: Tuesday, May 14, 2002 3:11 PM
To: Peter Darley
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Using COPY

create function fix_marker(text) returns text as '
  $_ = shift;
  s/@@~@@/\\n/;
  return $_;
' language 'plperl';

update tblFoo set foo=fix_marker(foo) where foo like '%@@~@@%';

would do nicely. Or you could do it in plpython, plruby (if installed), or
plperl.

You could do it in plpgsql with a combination of ugly left || right with
strpos, but it's a lot easier in languages that have string-replace
functions.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant


Re: Using COPY

From
"Corey W. Gibbs"
Date:
Morning Everyone,
I've had good luck with pgadmin II and it's database import wizard as well.
 The only problem I had was it wanted to make a memo field a varchar(1).
 One minor tweak, and i was able to pull 45 glorious megabytes of Access
data into PG.  We're slowly converting over a VB app to use said database,
and it's going really well.
ttfn
corey


-----Original Message-----
From:    Bertin, Philippe [SMTP:philippe.bertin@barco.com]
Sent:    Tuesday, May 14, 2002 8:17 AM
To:    tony; joe@jwebmedia.com
Cc:    pgsql-general@postgresql.org
Subject:    Re: [GENERAL] Using COPY

Tony,

Wouldn't the 'simple' way I thought of (i.e., using ODBC and Access
combined) do the trick ?

Regards,

Philippe Bertin.

> -----Original Message-----
> From:    tony [SMTP:tony@animaproductions.com]
> Sent:    dinsdag 14 mei 2002 16:30
> To:    joe@jwebmedia.com
> Cc:    pgsql-general@postgresql.org
> 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@postgresql.org so that your
> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Using COPY

From
"Joel Burton"
Date:
Well, you could, with something like

UPDATE tblFoo SET foo=
substring(foo from 1 for (position('@@~@@' in foo))-1) || '\n' ||
substring(foo from (position('@@~@@' in foo))+5) where foo like '%@@~@@%';

(Try saying that ten times fast! :) )

P.S. Fix the +5 above to match the length of the marker string.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter Darley
> Sent: Tuesday, May 14, 2002 6:15 PM
> To: Joel Burton
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Using COPY
>
>
> Joel,
>     Darn, I was hoping it was possible to do it with just SQL. :)
> Thanks,
> Peter Darley
>
> -----Original Message-----
> From: Joel Burton [mailto:joel@joelburton.com]
> Sent: Tuesday, May 14, 2002 3:11 PM
> To: Peter Darley
> Cc: pgsql-general@postgresql.org
> Subject: RE: [GENERAL] Using COPY
>
> create function fix_marker(text) returns text as '
>   $_ = shift;
>   s/@@~@@/\\n/;
>   return $_;
> ' language 'plperl';
>
> update tblFoo set foo=fix_marker(foo) where foo like '%@@~@@%';
>
> would do nicely. Or you could do it in plpython, plruby (if installed), or
> plperl.
>
> You could do it in plpgsql with a combination of ugly left || right with
> strpos, but it's a lot easier in languages that have string-replace
> functions.
>
> - J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>