pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Isabelle Brette <isabelle@apartia.fr>
To: Zhang, Anna <azhang@verisign.com>
Cc: 'pgsql-admin@postgresql.org' <pgsql-admin@postgresql.org>
Subject: Re: copy command
Date: Thu, 3 Jan 2002 19:16:39 +0100
Message-ID: <20020103181715.B95068794F@zenon.apartia.com> (raw)


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



view thread (7+ messages)  latest in thread

Message-ID: <20020103181715.B95068794F@zenon.apartia.com>
Permalink:  ../20020103181715.B95068794F@zenon.apartia.com/
Also on:    postgresql.org/message-id/20020103181715.B95068794F@zenon.apartia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: isabelle@apartia.fr, azhang@verisign.com
  Subject: Re: copy command
  In-Reply-To: <20020103181715.B95068794F@zenon.apartia.com>
  References: <20020103181715.B95068794F@zenon.apartia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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