Re: dos Dbase -> pg table] - Mailing list pgsql-novice

Tnx a lot, Frank.
Very usefull. So sorry about this new question. I run the script over a .dbf of about 4000 records in 30 columns.
when I run the script in this dbf this is the output:

home/fmaresca/DBFCONV/SUBSCRIB.DBF - "subscrib"
DBD::Pg::db do failed: PQsendQuery() -- query is too long.  Maximum length is 16382
Database handle destroyed without explicit disconnect.

Any idea? workaround?
Tnx,
Fernando.



On Thu, Jan 03, 2002 at 10:33:52AM -0500, Frank Bax wrote:
> install DBI, DBD:pg, DBD:XBase, then run this script (after customising the
> $dir, $dbf and $dbp lines):
>
> #!/usr/bin/perl -w
> use strict;
> use File::Basename;             # for basename() function
> use DBI;  use DBD::XBase;  use DBD::Pg;
> my $dir = '/home/fbax/DBFCONV';
> my $dbf = DBI->connect("dbi:XBase:$dir", {RaiseError => 1} );
> my $dbp = DBI->connect("dbi:Pg:dbname=fbax", "fbax", "", {RaiseError => 1} );
> while (my $fname = <$dir/*.DBF>) {
>   &DBF2PG ($dbf, $dbp, $fname, basename(substr($fname, 0, length($fname)-4)));
> }
> $dbf->disconnect;
> $dbp->disconnect;
>
> sub DBF2PG {
>   (my $dbf, my $dbp, my $fname, my $table) = @_;
>   $table = lc("\"$table\"");
>   print "$fname - $table\n";
>   open (PIPE, "dbfdump --info $fname |") or die "Can't open $fname: $!";
>   my $sql = "CREATE TABLE $table ";
>   my $sep = "(";
>   while( <PIPE> ) {
>     chomp;
>     if (/^[0-9]+\./) {          # line starts with number.
> #     print "$_\n";
>       my @stru = split;         # stru contains field,type,len,dec
>       $sql .= $sep.' "'.lc($stru[1]).'"';
>       if ($stru[2] eq 'D') {
>         $sql .= " date";
>       } elsif ($stru[2] eq 'L') {
>         $sql .= " boolean";
>       } elsif ($stru[2] eq 'M') {
>         $sql .= " text";
>       } elsif ($stru[2] eq 'G') {
>         $sql .= " text";
>       } elsif ($stru[2] eq 'C' && $stru[3] eq 1) {
>         $sql .= " char";
>       } elsif ($stru[2] eq 'C') {
>         $sql .= " varchar($stru[3])";
>       } elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 5) {
>         $sql .= " int2";
>       } elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 10) {
>         $sql .= " int4";
>       } elsif ($stru[2] eq 'N' && $stru[4] eq 0) {
>         $sql .= " int8";
>       } elsif ($stru[2] eq 'N') {
>         $sql .= " numeric($stru[3],$stru[4])";
>       } else {
>         $sql .= " $stru[2].$stru[3].$stru[4]";
>       }
>       $sep = ',';
>     }
>   }
>   close (PIPE);
>   $sql .= ' );';
>   $dbp->{RaiseError} = 0;
>   $dbp->do( "DROP TABLE $table" );
>   $dbp->{RaiseError} = 1;
>   $dbp->do( $sql );
>
>   my $sth = $dbf->prepare(" SELECT * FROM ".basename($fname) );
>   $sth->execute;
>   while (my @row = $sth->fetchrow_array()) {
>     $sql = "INSERT INTO $table VALUES ";
>     $sep = "(";
>     foreach my $fld (@row) {
>       $sql .= "$sep ".$dbp->quote($fld);
>       $sep = ",";
>     }
>     $sql .= ' );';
>     $dbp->do( $sql );
>   }
>   $sth->finish;
> }
>
>
> At 08:44 AM 1/3/02 -0300, Fernando M. Maresca wrote:
> >Hello everybody,
> >
> >There is out there a proggy called dbf2pg (like dbf2mysql) that makes a
> good job, but it can not deal with MEMO
> >fields. AHHGGG!!!
> >So, somebody knows a program that can deal with MEMO fields in (DOS) .dbf
> files, or, at least, a program that can
> >cut off memo fields of a .dbf file from command line in *nix?
> >The idea is to build a script (well, I wrote it and run ok if there are no
> MEMOs in source .dbf) that cron.ically
> >updates a table from a dbase file.
> >Tnx, regards
> >
> >
> >--
> >Fernando M. Maresca
> >
> >Monitoring Station S.A.
> >48 nº 812 La Plata (1900) BA - ARG
> >Tel/Fax: (54) 221 425-3355
> >ICQ: 101304086
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/users-lounge/docs/faq.html
> >
>