Thread: Import DB from DOS- dbase4
Hi! 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? thanks Fernando M. Maresca Monitoring Station S.A. 48 n° 812 La Plata - BA - ARG Tel/Fax: (54) 221 425 3355 ICQ: 101304086
Fernando,
> 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?
No, unfortunately. You have two possible methods:
1. Dump the Dbase4 (using the original application) data into a
delimited text file, and then use COPY to load it into Postgres.
2. Use ODBC (or UnixODBC) and an external language (like Java, Perl, or
Python) or database tool (like MS Access, Kylix, or Rekall) to pull data
out of DBase4 and pump it into Postgres.
Good luck!
-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
Hi I have imported dBASEIV tables into Postgresql. I exported the tables from dBASEIV as delimited text files and then copied in Postgresql. In some table I needed to drop a field that was to become sequence in Postgresql. Using gawk script I removed the column, converted the records into sql insert query and let Postgresql assign sequential numbers. Used gawk script once again. Now all new records get numbered automatically. If you are interested, I could send to you gawk and "C" scripts that I used . Vijay "Fernando M. Maresca" wrote: > > Hi! > 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? > thanks
Vijay, > I have imported dBASEIV tables into Postgresql. I exported the > tables > from dBASEIV as delimited text files and then copied in Postgresql. > In > some table I needed to drop a field that was to become sequence in > Postgresql. Using gawk script I removed the column, converted the > records into sql insert query and let Postgresql assign sequential > numbers. Used gawk script once again. Now all new records get > numbered > automatically. If you are interested, I could send to you gawk and > "C" > scripts that I used . Us folks in the PostgreSQL community would really, really appreciate it if you put together all of the above as a text or HTML document, and then submitted it to: http://techdocs.postgresql.org/ Know-how like yours is exactly what we need on the site! -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
I've just discovered that password protection for phpPgAdmin may not be functioning if the postgres config file isn't set to require passwords. It's sure easy to check, just type in postgres as a username and a bogus password and it still works! The quick, but dirty, fix is to change the default directory to some other name. Frank Hilliard http://frankhilliard.com/
Josh I was thrilled to receive your mail. I will get busy putting things together and submit to techdocs as soon as possible. Vijay Josh Berkus wrote: > > Vijay, > Us folks in the PostgreSQL community would really, really appreciate it > if you put together all of the above as a text or HTML document, and > then submitted it to: > http://techdocs.postgresql.org/ > Know-how like yours is exactly what we need on the site! > -Josh
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; }
Given 3 tables: --------------- Table Operations(Op) Op_ID, Op_Name,... and Table TravelerHEADER(TravH) TravH_ID, TravName, Part_ID,... and Table TravelerDETAIL(TravD) TravD_ID, TravH_ID, Op_ID, TravOrder,... How can I retrieve the OP RecordSet associated with the subset of TravD records given a single TravH_ID? That is to say: There are a finite set of travd records that relate to a singular travh record... for each of those travd records I wantthe related Op record. Stumped again, Brian