Re: Import DB from DOS- dbase4
От | Frank Bax |
---|---|
Тема | Re: Import DB from DOS- dbase4 |
Дата | |
Msg-id | 3.0.6.32.20011222111038.0079c350@pop6.sympatico.ca обсуждение исходный текст |
Ответ на | Import DB from DOS- dbase4 ("Fernando M. Maresca" <fmaresca@monssa.com.ar>) |
Ответы |
(Brian <Brrrian@Excite.com>)
|
Список | pgsql-novice |
At 06:50 PM 12/15/01 -0300, Fernando M. Maresca wrote: >i working around an application with Postgres as DB server, but the >data for now comes from a DOS app. and the file format of the >tables is dbase 4; so, can i import these tables directly into >postgres tables automatically? Are there some kind of utility? Conversion of data from xBase to PostgreSQL is (almost) trivial. Just install DBI, DBD:Pg and DBD:xBase, and write a little read/write routine. http://search.cpan.org/search?module=DBI http://search.cpan.org/search?module=DBD::XBase *DBD::XBase supports many dBase variants. *I can't seem to find DBD:Pg at CPAN today?? The "dbfdump" utility (to get DBF header info) is included in DBD:XBase. Just ftp all your dbase files into a single directory ($dir below). #!/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; }
В списке pgsql-novice по дате отправления: