Re: error-tolerant COPY FROM

Mike Rylander zei:
> On Fri, 4 Feb 2005 13:32:40 +0100 (CET), Joolz
> <joolz(at)arbodienst-limburg(dot)nl> wrote:
>> Hello everyone,
>>
>> I'm building a postgresql db which will have to get lots of data
>> from "the outside" (customers, that is). The db has lots of
>> constraints, and I'm sure that our customers will offer lots of
>> invalid information. We receive the information in csv format. My
>> first thought was to read them into the database with COPY, but
>> "COPY stops operation at the first error."
>>
>> What I need is an import where all valid lines from the csv files
>> are read into the db, and I also get a logfile for all invalid
>> lines, stating the line number plus the pg error message so I can
>> see which constraint was violated.
>>
>> I can't think of a direct, elegant solution for this, does anyone
>> have any suggestions? Thanks a lot!
>
> How about a Perl script that uses DBI with autocommit turned on and
> loops over the file using (something like) 'while (my @line =
> split(',',scalar(<>)) {}'?
>
> Perl tracks the line number of the current input file (including
> STDIN) in $. (dollar-period). If you get a DBI error, 'warn "Input
> error at line $.\n";'.

Thanks Mike, someone else suggested the same (and help :)).

If is has to be perl, so be it, although I'm not a big fan. Do you
think this is possible in python?