Received: from zenon.apartia.com (APoncelet-101-2-1-171.abo.wanadoo.fr [193.251.42.171]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g03IHNm58778 for ; Thu, 3 Jan 2002 13:17:24 -0500 (EST) (envelope-from isabelle@apartia.fr) Received: from localhost (argos.apartia.int [10.0.2.1]) by zenon.apartia.com (Postfix) with SMTP id B95068794F; Thu, 3 Jan 2002 19:17:15 +0100 (CET) Received: by localhost (sSMTP sendmail emulation); Thu, 3 Jan 2002 19:16:39 +0100 Date: Thu, 3 Jan 2002 19:16:39 +0100 From: Isabelle Brette To: "Zhang, Anna" Cc: "'pgsql-admin@postgresql.org'" Subject: Re: copy command Message-Id: <20020103181715.B95068794F@zenon.apartia.com> X-Archive-Number: 200201/14 X-Sequence-Number: 2903 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