asked on

read file into database

hello experts,

I need to read file into ma DBI database using perl script. A have file usage.fin which contains :

20k:w4293
20k:w4304
20k:w4324
20k:w4339
20k:w4369
1.3M:w4400
20k:w4412

also I have DBI database file with flields :

#cat space_usage
name,usage

I have DBI driver installed and I'm able to add entry to dabase with script like this:

#cat add_values
#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=.")
or die "Cannot connect: " . $ $DBI::errstr;
$dbh->do( "insert into space_usage values ('w2601','1550MB')");

then database :

#cat space_usage
name,usage
w2601,1550MB

--------------------------

What I need is read file usage.fin into add_values script and add all entries form file to my two databese fields. So my database will look like this:

cat space_usage
name,usage
w4293,20k
w4304,20k
w4324,20k
w4339,20k
w4369,20k
w4400,1.3M
w4412,20k

What I done until now is that I can read file into script but I need to split string "20k:w4293" into two varilables like $name and $usage and make loop with
$dbh->do( "insert into space_usage values ($name,$usage)");

I have a look on split command but not sure how to use it in my case . I'm very beginner so maybe there is another solution how to do same result so if somebody have better way please let me know please.. I'll award points to most informative answer and best solution.

thanks

./lubo

SOLUTION

gripe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gripe

Actually, i doubt it will make a difference with DBD::CSV, but this would be better. Note the prepare outside of the while loop:

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=.")
or die "Cannot connect: " . $ $DBI::errstr;

open my $file, "data.txt" or die $!;

my $sth = $dbh->prepare('insert into space_usage values(?, ?)');

while (<$file>) {
@vals = split /,/;
$sth->execute(@vals);
}

Luxana

ASKER

this is not what I need !

When I use your script my database look like this:

cat space_usage
name,usage
"600k:w2171
"
"20k:w2708
"
"20k:w2739
"
"20k:w2876
"
"20k:w3036

look above for my desire output of database.

ozo

Comment from gripe
Date: 10/22/2004 07:50PM PDT
      Comment

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=.")
or die "Cannot connect: " . $ $DBI::errstr;

open my $file, "data.txt" or die $!;

while (<$file>) {
@vals = split /,/;
$sth = $dbh->prepare('insert into space_usage values(?, ?)');
$sth->execute(@vals);
}

Comment from gripe
Date: 10/22/2004 08:06PM PDT
      Comment

Actually, i doubt it will make a difference with DBD::CSV, but this would be better. Note the prepare outside of the while loop:

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:CSV:f_dir=.")
or die "Cannot connect: " . $ $DBI::errstr;

open my FILE, "<data.txt" or die $!;

my $sth = $dbh->prepare('insert into space_usage values(?, ?)');

while( <FILE> ){
chomp;
@vals = split /:/;
$sth->execute(@vals);
}

Comment from Luxana