Re: copy command

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(at)apartia(dot)fr