Thread: Need help with COPY

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?

Jonathan Hseu

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.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

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!

Jonathan Hseu wrote:

> 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?
>
> Jonathan Hseu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

reformat such input using sed:

cat yourfile |  sed -e 's/"/'\''/g' >yournewfile

For readability : sed -e sq/dq/sq\sqsq/gsq  in which sq = single quote dq = double
quote

Vi using regexps is also a good one.

or using perl:
while (<STDIN>) {
   chomp();
   s/"/'/g;
  print $_, "\n";
}


Feite Brekeveld