Re: filemaker to pgsql ?

At 3:06 PM -0500 11/6/00, Isaac wrote:
>hello,
>
>has anyone converted files from filemaker to postgres? I'm figuring I'll
>just export everything into tabbed text files and then use the perl
>extensions to parse it out into INSERT queries. If anyone has any experience
>(or code!) to share on this process, it would be great to hear about it.
>
>--i

Here is a very unfinished/unpolished (but working) script to insert
comma delimited files into postgres tables using perl:

#!/usr/bin/perl
#
# Tool to import tab or comma delimited files into a database
#
# Start with comma
#
#print "Filename:";
#chomp ($file = <>);

use DBI;

$file = "participants.txt";

open (FILE, "$file");
@lines = <FILE>;

#assume the first row are column names that conform to db field names
$colnames = shift (@lines);

@cols = split (',', $colnames);
$num_cols = $#cols;

$dbname = "st";
$table = "profile";

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname","","") or die "Can't
connect to database\n";

foreach (@lines) {

#Put the lines in a hash
%rows = (); $i=0;
@data = split (',');
foreach $col (@cols) {
$rows{$col} = $data[$i];
$i++;
}

# Build a SQL statement to insert each line from file

$i=0;
$sql0 = "INSERT INTO $table (";
@sqla = (); @sqlb = ();
foreach $key (keys %rows) {
$sqla[$i] = $key;
$sqlb[$i] = $rows{$key};
$i++;
}

$sql1 = join (',',@sqla);
$sql2 = ") VALUES ('";
$sql3 = join ('\',\'',@sqlb);
$sql4 = "')";
$sql = "$sql0$sql1$sql2$sql3$sql4";

print "SQL:$sql\n";

my $sth = $dbh->prepare("$sql") or die "Can't prepare SQL
statement: $DBI::errstr\n";
$sth->execute or die "Can't execute: $DBI::errstr\n";
$sth->finish;

}

$dbh->disconnect;
--
--------------------------------
Michelle Murrain Ph.D., President
Norwottuck Technology Resources
mpm(at)norwottuck(dot)com
http://www.norwottuck.com