Re: Import dbf data
От | Frank Bax |
---|---|
Тема | Re: Import dbf data |
Дата | |
Msg-id | 5.2.1.1.0.20050923094533.03085020@pop6.sympatico.ca обсуждение исходный текст |
Ответ на | Import dbf data (Rafael Barbosa <rrbarbosa@gmail.com>) |
Список | pgsql-novice |
At 03:22 PM 9/22/05, Rafael Barbosa wrote: >I've decide to use PostgreSql as the SGBD for the application i'm >developing. I've already have some info based on dbf files. Someone >has any idea on how can i use the info on this dbf files? >If only i could extract de info of the the dbf, i could make the >database schemas and the genarate "insert into" script using the >information extracted of the dbf. From http://www.cpan.org/ you can install: DBI - 1.48 DBD::Pg - 1.41 DBD::XBase - 0.241 Then write a perl script to access dbf file with sql statements. This might help get you started: #!/usr/bin/perl -w use strict; use DBI; use DBD::XBase; use DBD::Pg; use File::Basename; # for basename() function my $base = shift; my $dir = '/home/FamTree/' . $base . '/data'; my $dbf = DBI->connect("dbi:XBase:$dir", {RaiseError => 1} ); my $dbp = DBI->connect("dbi:Pg:dbname=famtree", "famtree", "", {RaiseError => 1} ); while (my $fname = <$dir/$base*.DBF>) { &DBF2PG ($dbf, $dbp, $fname, basename(substr($fname, 0, length($fname)-4))); } $dbf->disconnect(); $ cat scripts/DBF2PG.pl sub DBF2PG { (my $dbf, my $dbh, 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])"; } elsif ($stru[2] eq 'I' && $stru[4] eq 0 && $stru[3] eq 4) { $sql .= " int4"; } else { $sql .= " $stru[2].$stru[3].$stru[4]"; } $sep = ','; } } close (PIPE); $sql .= ' );'; $dbh->{RaiseError} = 0; $dbh->{PrintError} = 0; $dbh->do( "DROP TABLE $table" ); $dbh->{RaiseError} = 1; $dbh->{PrintError} = 1; $dbh->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 ".$dbh->quote($fld); $sep = ","; } $sql .= ' );'; $dbh->do( $sql ); } $sth->finish; } 1;
В списке pgsql-novice по дате отправления: