Thread: filemaker to pgsql ?

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

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@norwottuck.com
http://www.norwottuck.com

Yes, I just did the awhile back :)
Its time stamped and outputs a file which can be globbed in via. psql.  This
way you can check for errors before you go insert.

Yann


Here is my version of the script:

#!/usr/bin/perl

#dvf=> \d products
##            Table "products"
#   Attribute   |     Type     | Modifier
#---------------+--------------+----------
# product       | varchar(20)  |
# owner         | varchar(100) |
# add_date      | bigint       |
# collection    | varchar(50)  |
# camera_format | varchar(50)  |
# clip_cost     | float8       |
# duration      | bigint       |
# film_date     | bigint       |
# location      | varchar(50)  |

sub do_opt {
    my ($product, $field, @loop) = @_;
    my $val;
    foreach $val (@loop) {
        if ($val) {
            print "INSERT INTO product_opt (product, key, value) VALUES
('$product', '$field', '$val');\n";
        }
    }
}

while(<>) {
        my @input = split(/\t/);
        my $now = time;
        my $product = $input[0];
        print "INSERT INTO products (product, owner, add_date, collection,
camera_format, clip_cost, duration, film_date, location) VALUES ('$input[0]',
'person\@redshift.com', $now, '$input[14]', '$input[3]', $input[4],
$input[8], '$input[6]', '$input[10]');\n";
        my @altitude = split(/[\cK]/, $input[1]);
        do_opt($product, 'altitude', @altitude);
        my @behavior = split(/[\cK]/, $input[2]);
        do_opt($product, 'behavior', @behavior);
        my @common = split(/[\cK]/, $input[5]);
        do_opt($product, 'common', @common);
        my @key = split(/[\cK]/, $input[7]);
        do_opt($product, 'keyword', @key);
        my @habitat = split(/[\cK]/, $input[9]);
do_opt($product, 'habitat', @habitat);
        my @subject = split(/[\cK]/, $input[13]);
        do_opt($product, 'subject', @subject);
        my @compo = split(/[\cK]/, $input[11]);
        do_opt($product, 'composition', @compo);





}

On Mon, 06 Nov 2000, you (Isaac) might of written:
> 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

--

--------------------------------------------------------------------
Yann Ramin            atrus@atrustrivalie.eu.org
Atrus Trivalie Productions    www.redshift.com/~yramin
AIM                oddatrus
Marina, CA            http://profiles.yahoo.com/theatrus

IRM Developer                   Network Toaster Developer
SNTS Developer                  KLevel Developer
Electronics Hobbyist        person who loves toys

Build a man a fire, and he's warm for a day.
Set a man on fire, and he'll be warm for the rest of his life.

"I'm prepared for all emergencies but totally unprepared for everyday
life."
--------------------------------------------------------------------