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(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!