Re: Need help with COPY

On Sat, Jun 16, 2001 at 02:46:26PM +0200, Peter Eisentraut wrote:
> Jonathan Hseu writes:
>
> > I want to import a table from a file.  The format of the table in the file is as
> > such:
> > "FName","LName","Address","City"
> >
> > The format of the table is:
> > (FName varchar(80), LName varchar(80), Address varchar(80), City varchar(80))
> >
> > I want to do a:
> > COPY table FROM 'filename' USING DELIMITERS ','
> >
> > The problem is, it also imports the quotes.  Also, there are sometimes commas in
> > the address, and it takes those as delimiters.  Is there any way to make it take
> > only the stuff within the quotes and ignore any delimiters within them?
>
> You need to reformat your input files (strip quotes and use different
> delimiter) or not use the COPY command.

or -- if ALL fields are quoted (none are barewords) -- then just
use PERL (the "swiss army chainsaw" of text mungers) to
translate:

    #!/usr/bin/perl
    # note: untested, might possibly reformat your disk--
    while( <> ) {
        chomp;
        print join "\t", eval qq:($_): ; # note () INSIDE delimiters
        print "\n";
    }

run that via

    $ perl quick-csv-to-tab-fixer.pl < quoted-csv-file > tab-separated-file

then

    $ psql mydatabase
    > copy sometable from tab-separated-file ;

=====

or, for short,

    perl -pe "$_=join qq(\t),eval qq'($_)' ; $_.=qq(\n)" \
        < csv-file \
        > tab-file

--
I figure: if a man's gonna gamble, may as well do it
without plowing.   -- Bama Dillert, "Some Came Running"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!