Postgres Pro
Downloads
Home   >   mailing lists

Thread: dos Dbase -> pg table

dos Dbase -> pg table

From
"Fernando M. Maresca"
Date:
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

Re: dos Dbase -> pg table

From
Frank Bax
Date:
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
>

Re: dos Dbase -> pg table

From
Frank Bax
Date:
The problem is that the INSERT statment generated by the script is more 16K
long.  You must have some very large memo fields!  This a pgsql limitation,
but I think it has been increased(?) in recent versions?  What version are
you on?

I don't know of an easy workaround, but I can think of two options:

1) write a custom version of the script for this file which first does an
INSERT on some of the fields, followed by an UPDATE to put the rest in.
Hopefully you have a unique key in the file for the where clause of UPDATE
portion.  Remember to do a VACCUM at the end to recover space no longer
used; since the space used by INSERT will not be reused when you issue UPDATE.

2) rewrite the portion of script which generates INSERT to instead create
an tempfile with data in a format expected by "COPY FROM <tempfile>".
Then, once the tempfile has all your data, issue "COPY FROM <tempfile>".
    http://looking-glass.usask.ca:82/users-lounge/docs/7.0/user/sql-copy.htm

If a 16K limitation on SQL statements will be a problem with your
applications, you might consider making a change to source code and
re-installing; this is out of my league.

Frank


At 02:24 PM 1/3/02 -0300, Fernando M. Maresca wrote:
>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
>> >
>>
>
>--
>Fernando M. Maresca
>
>Monitoring Station S.A.
>48 nº 812 La Plata (1900) BA - ARG
>Tel/Fax: (54) 221 425-3355
>ICQ: 101304086
>

DATE_ADD, DATE_SUB, ETC

From
Burra
Date:
Does postgres have functions that are similar to these mysql functions?

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)

--------------------[-- burra@colorado.edu --]--------------------------

Re: DATE_ADD, DATE_SUB, ETC

From
Jason Earl
Date:
Uh, I think that you would be surprised what PostgreSQL will let you
do without special functions.

For example:

SELECT '2001-01-01'::timestamp + '1 year'::interval;

        ?column?
------------------------
 2002-01-01 00:00:00-07


You don't even need the explicit casts if the database knows the type
already.  Subtracting timestamps gets you an interval, and intervals
and timestamps can be added (or subtracted) to get a timestamp result.

Jason

Burra <burra@colorado.edu> writes:

> Does postgres have functions that are similar to these mysql functions?
>
> DATE_ADD(date,INTERVAL expr type)
> DATE_SUB(date,INTERVAL expr type)
> ADDDATE(date,INTERVAL expr type)
> SUBDATE(date,INTERVAL expr type)
>
> --------------------[-- burra@colorado.edu --]--------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: DATE_ADD, DATE_SUB, ETC

From
"Josh Berkus"
Date:
Burra,

> DATE_ADD(date,INTERVAL expr type)
> DATE_SUB(date,INTERVAL expr type)
> ADDDATE(date,INTERVAL expr type)
> SUBDATE(date,INTERVAL expr type)

Please see the FAQ at:
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco