pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
copy command
7+ messages / 7 participants
[nested] [flat]

* copy command
@ 2002-01-03 17:10 Zhang, Anna <azhang@verisign.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Zhang, Anna @ 2002-01-03 17:10 UTC (permalink / raw)
  To: pgsql-admin

I have a problem to load data to postgres database using copy command. The
problem is that we have one column called address which is multi-line text,
the taxt file looks like this:

aab770|awkc.com administration|sultan 23
Bogota, na00000|CO

above shows one record with '|' as delimiters. Column 3 has two lines which
caused the record is splited into two lines. If I use copy command to load
this record, it is always treated as two records. Is there any way to tell
postgres to read until reach a specified char? not stop at '\n'?
Or maybe edit the text file to make it one line, but how?  We have milions
of such records in the text file.
Anyone can provide a solution, thanks a million!

Anna Zhang




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: copy command
@ 2002-01-03 18:16 Isabelle Brette <isabelle@apartia.fr>
  2002-01-03 19:11 ` Re: copy command DHSC Webmaster <webmaster@dhs-club.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Isabelle Brette @ 2002-01-03 18:16 UTC (permalink / raw)
  To: Zhang, Anna <azhang@verisign.com>; +Cc: pgsql-admin


Hello,

On Thu, Jan 03, 2002 at 12:10:01PM -0500, Zhang, Anna wrote:
> I have a problem to load data to postgres database using copy command. The
> problem is that we have one column called address which is multi-line text,
> the taxt file looks like this:
> 
> aab770|awkc.com administration|sultan 23
> Bogota, na00000|CO
> 
> above shows one record with '|' as delimiters. Column 3 has two lines which
> caused the record is splited into two lines. If I use copy command to load
> this record, it is always treated as two records. Is there any way to tell
> postgres to read until reach a specified char? not stop at '\n'?
> Or maybe edit the text file to make it one line, but how?  We have milions
> of such records in the text file.

You can use some powerful editor, such as VI, to recognize any line that
does not begin with a proper pattern, and join it with the one before.
Not to lose the line feed you'll probably have to add something to
replace it (such as, let's say, a tab) and convert it back once you've
imported (postgres has some nice pattern-matching features).

I've done this before to prepare an import (through a perl script,
because I needed the information previously in one table to be split
within several ones, but this does not make a major difference, because
a perl script only reads one line at a time), the condition is that the
beginning of each line is easily recognizable. If your first column is
_not_ easily recognizable, maybe you can find a pattern showing a line
is incomplete and join with the next line (bad number of |, for
example).

Hope I've been clear enough (I'm not that good at explaining things in
English) and that it helps.

-- 
Isabelle Brette - isabelle@apartia.fr



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: copy command
  2002-01-03 18:16 Re: copy command Isabelle Brette <isabelle@apartia.fr>
@ 2002-01-03 19:11 ` DHSC Webmaster <webmaster@dhs-club.com>
  0 siblings, 0 replies; 7+ messages in thread

From: DHSC Webmaster @ 2002-01-03 19:11 UTC (permalink / raw)
  To: Zhang, Anna <azhang@verisign.com>; +Cc: pgsql-admin



Isabelle Brette wrote:
> 
> Hello,
> 
> On Thu, Jan 03, 2002 at 12:10:01PM -0500, Zhang, Anna wrote:
> > I have a problem to load data to postgres database using copy command. The
> > problem is that we have one column called address which is multi-line text,
> > the taxt file looks like this:
> >
> > aab770|awkc.com administration|sultan 23
> > Bogota, na00000|CO
> >
> > above shows one record with '|' as delimiters. Column 3 has two lines which
> > caused the record is splited into two lines. If I use copy command to load
> > this record, it is always treated as two records. Is there any way to tell
> > postgres to read until reach a specified char? not stop at '\n'?
> > Or maybe edit the text file to make it one line, but how?  We have milions
> > of such records in the text file.
> 
> You can use some powerful editor, such as VI, to recognize any line that
> does not begin with a proper pattern, and join it with the one before.
> Not to lose the line feed you'll probably have to add something to
> replace it (such as, let's say, a tab) and convert it back once you've
> imported (postgres has some nice pattern-matching features).
> 
> I've done this before to prepare an import (through a perl script,
> because I needed the information previously in one table to be split
> within several ones, but this does not make a major difference, because
> a perl script only reads one line at a time), the condition is that the
> beginning of each line is easily recognizable. If your first column is
> _not_ easily recognizable, maybe you can find a pattern showing a line
> is incomplete and join with the next line (bad number of |, for
> example).
> 
> Hope I've been clear enough (I'm not that good at explaining things in
> English) and that it helps.
> 
> --
> Isabelle Brette - isabelle@apartia.fr
>

If you use a perl script to 'count' your columns, you can escape your
newlines with a \ in your addresses and postgres will transparently
convert them to newlines in the data. By doing this it will process more
than one line in your text file as a row.

network=# create table tmp2 (id int, name text, address text);
CREATE
network=# insert into tmp2 (id,name,address) values(100,'bill','456
harmony ln.
network'# hickup, NH');
INSERT 86195110 1
network=# select * from tmp2;
 id  | name |          address
-----+------+----------------------------
 100 | bill | 456 harmony ln.
hickup, NH
(1 row)

Insert another row.

network=# insert into tmp2 (id,name,address)values(200,'Sam','Is
linebreak here?\newline');
INSERT 86195203 1
network=# select * from tmp2;
 id  | name |           address
-----+------+-----------------------------
 100 | bill | 456 harmony ln.
hickup, NH
 200 | Sam  | Is linebreak here?
ewline
(3 rows)

Here is the export file I copied out from tmp2
100|bill|456 harmony ln.\
hickup, NH
200|Sam|Is linebreak here?\
ewline

This file imported correctly to another table with the newlines embedded
in the data.

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
Bill MacArthur
Webmaster
DHS Club



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* copy command
@ 2005-10-20 23:05 Sidar López Cruz <sidarlopez@hotmail.com>
  2005-10-21 07:22 ` Re: copy command Hannes Dorbath <light@theendofthetunnel.de>
  0 siblings, 1 reply; 7+ messages in thread

From: Sidar López Cruz @ 2005-10-20 23:05 UTC (permalink / raw)
  To: pgsql-admin

how to copy data from file with fixed columns width to postgresql table?

_________________________________________________________________
MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: copy command
  2005-10-20 23:05 copy command Sidar López Cruz <sidarlopez@hotmail.com>
@ 2005-10-21 07:22 ` Hannes Dorbath <light@theendofthetunnel.de>
  0 siblings, 0 replies; 7+ messages in thread

From: Hannes Dorbath @ 2005-10-21 07:22 UTC (permalink / raw)
  To: pgsql-admin

On 21.10.2005 01:05, Sidar López Cruz wrote:
> how to copy data from file with fixed columns width to postgresql table?

About 3-4 lines in your favorite scripting language should do. Besides 
this many frontends can do, EMS PG Manager being one of them.


-- 
Regards,
Hannes Dorbath



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* FW: copy command
@ 2006-01-26 10:50 Aniko.Badzong@swisscom.com
  2006-01-26 11:02 ` Re: FW: copy command Stef <svb@ucs.co.za>
  0 siblings, 1 reply; 7+ messages in thread

From: Aniko.Badzong@swisscom.com @ 2006-01-26 10:50 UTC (permalink / raw)
  To: pgsql-admin



> Hi
> 
> I have tried it yesterday to escaoe the double qutoes and I get the
> error:
> 
> [test] su - postgres -c "echo \"copy test from '/data/log/bla';"\"  |
> psql logdb"
> Unmatched ".
> 
> somehow does not want to work.
> 
> 
> 
> 
> RE: [SQL] psql in the command line
> from: Russell Simpkins [russellsimpkins@hotmail.com]
> if in fact this works: echo "copy test from '/data/log/bla';" | psql
> logdb
> then you probably want to write it like this:
> su - postgres -c "echo \"copy test from '/data/log/bla';\"|>psql
> logdb"
> so that you escape the double quotes.
> 
> 

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: FW: copy command
  2006-01-26 10:50 FW: copy command Aniko.Badzong@swisscom.com
@ 2006-01-26 11:02 ` Stef <svb@ucs.co.za>
  0 siblings, 0 replies; 7+ messages in thread

From: Stef @ 2006-01-26 11:02 UTC (permalink / raw)
  To: Aniko.Badzong@swisscom.com; +Cc: pgsql-admin

Aniko.Badzong@swisscom.com mentioned :
=> 
=> 
=> > Hi
=> > 
=> > I have tried it yesterday to escaoe the double qutoes and I get the
=> > error:
=> > 
=> > [test] su - postgres -c "echo \"copy test from '/data/log/bla';"\"  |
=> > psql logdb"
=> > Unmatched ".
=> > 
=> > somehow does not want to work.

su - postgres -c "echo \"copy test from '/data/log/bla';\" | psql logdb "

-- 
Confidentiality Notice:

The above message and all attachments may contain privileged and
confidential information intended only for the person or entity to which it
is addressed. Any review, retransmission, dissemination, copy or other use
of, or taking of any action in reliance upon this information by persons or
entities other than the intended recipient is prohibited. If you received
this message in error, please notify the sender immediately by e-mail,
facsimile or telephone and thereafter delete the material from your
computer. Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be the view of
the entity transmitting the message. UCS Group Limited and all of its
subsidiary companies hereby distances itself from and accepts no liability
in respect of the unauthorized use of its e-mail facility or the sending of
e-mail communications for other than strictly business purposes



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-01-26 11:02 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-03 17:10 copy command Zhang, Anna <azhang@verisign.com>
2002-01-03 18:16 Re: copy command Isabelle Brette <isabelle@apartia.fr>
2002-01-03 19:11 ` DHSC Webmaster <webmaster@dhs-club.com>
2005-10-20 23:05 copy command Sidar López Cruz <sidarlopez@hotmail.com>
2005-10-21 07:22 ` Hannes Dorbath <light@theendofthetunnel.de>
2006-01-26 10:50 FW: copy command Aniko.Badzong@swisscom.com
2006-01-26 11:02 ` Stef <svb@ucs.co.za>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox