Thread: 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
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
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. > -----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
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
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
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
> -----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.
>
>
>
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
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
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
>