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

LVL 10
LuxanaAsked:
Who is Participating?
 
ozoCommented:
while( <FILE> ){
     chomp;
     $sth->execute(reverse split/:/);
}
0
 
gripeCommented:
#!/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);
}
0
 
gripeCommented:
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);
}
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
LuxanaAuthor Commented:
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.
0
 
ozoCommented:
     
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
0
 
LuxanaAuthor Commented:
ozo I noticed that you changed gripe commenta bit, separator in @cals = split to ":"

database now looks like:

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

but notice that field name contain usage value and field usage contain name value. How tu make it oposite?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.