• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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

0
Luxana
Asked:
Luxana
  • 2
  • 2
  • 2
2 Solutions
 
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
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
 
ozoCommented:
while( <FILE> ){
     chomp;
     $sth->execute(reverse split/:/);
}
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now