Re: Import csv file into multiple tables in Postgres

On Mar 3, 2005, at 3:10 PM, Deepblues wrote:

> Thanks for all that information, My scenario looks like this ...
>
> I need to import an excel spreadsheet into the postgresql database .
> I converted the excel spreadsheet into a csv file and now I have 3
> tables in my database where I need to import this data. Im new to both
> perl and postgres. do you have any sample script that I can have a
> look at which loads the csv file into a interface table ?
>

Just an example....

It will pay to read the DBI manual if you are going to use perl like
this. You can probably just use the psql \copy command, though.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Pg:dbname=qiagen2;host=localhost',
'username',
'password',
{AutoCommit => 1},
);

open (IN,'<yourdatafile.txt');

my $sql = qq{CREATE TABLE g_rif (
g_rif_id serial primary key,
gene_id integer,
rif text not null,
pmid integer not null
)};
$dbh->do($sql);

$sql = qq{COPY g_rif (gene_id,rif,pmid) from STDIN};
my $sth = $dbh->prepare($sql);
$sth->execute() || die $sth->errstr;
while (<IN>) {
chomp;
my @params=split/,/;
# next line does the insertion, but with columns rearranged for
convenience....
my $ret =
$dbh->func(join("\t",$params[1],$params[4],$params[2])."\n",
'putline');
}
$dbh->func('endcopy');
close IN;

Sean