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/