Re: Off topic? - Solution for a delimiter problem

If space was used as the delimiter without accounting
for the quotation marks, text fields might be broken
apart whenever a space occurred.  For example, the
street address "123 Sesame Street" might be split into
3 fields containing '"123', 'Sesame' and 'Street"'
respectively.  In the past, I imported the file into
MySQL, then exported the data into a tab delimited
text file for importing into PostgreSQL.

The Python script saves many steps; so I was offering
it up for use and improvement by others.  I didn't
know if I should post the script to the mailing list
or send it directly to people who are interested.
(It's only 1k.)

Andrew Gould

--- "Poul L. Christiansen" <poulc@cs.auc.dk> wrote:
> Maybe you can specify the delimiter to be space.
>
> radius=# \h copy
> Command:     COPY
> Description: Copies data between files and tables
> Syntax:
> COPY [ BINARY ] table [ WITH OIDS ]
>     FROM { 'filename' | stdin }
>     [ [USING] DELIMITERS 'delimiter' ]
>     [ WITH NULL AS 'null string' ]
> COPY [ BINARY ] table [ WITH OIDS ]
>     TO { 'filename' | stdout }
>     [ [USING] DELIMITERS 'delimiter' ]
>     [ WITH NULL AS 'null string' ]
>
> That would probably be COPY table FROM filename
> USING DELIMITERS ' ';
>
> HTH,
> Poul L. Christiansen
>
> Andrew Gould wrote:
>
> > I receive space delimited files that have spaces
> > within double quoted text fields.
> >
> > Since PostgreSQL's COPY doesn't deal with multiple
> > delimiters, I've written a crude Python script to
> > convert the files to a tab delimited format
> without
> > the double quotes and without disturbing the
> spaces
> > within text fields.  I'm sure someone could make
> it
> > more efficient.  (Probably by rewriting it in
> Perl.)
> >
> > Does anyone else need the script?  Is the script
> > something I can/should post?
> >
> > Andrew Gould

__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/