Using COPY

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.

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

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

Tony,

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

Regards,

Philippe Bertin.

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

-----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

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

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

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.

-----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

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

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.