japhySnyder has asked for the wisdom of the Perl Monks concerning the following question:

i'm a dba and just started learning perl (and programming) about week ago...i am hoping some kind soul will help me out.

i'm working on a script to take the results of a benchmark result text file, parse the text, then toss it into a mysql database. the text file is composed of six columns and is many lines long. an example of a line is: count_distinct 25.00 0.48 0.12 0.60 1000

in pseudoCodeSpeak, i wanna grab the file, pass it into an array, grab the first line, parse the line into its six elements, write them to a row in a mysql table which i've already created, then do it again for the next line, then the next, and so on and so forth until there are no lines left. the mysql insert using perl is no big deal, i found an example that works that i'm trying to adapt - but the perl i have is very much incomplete, like there is no reference to the subroutine yet :)

any help is greatly, greatly appreciated. i'm planning on learning perl one step at a time, but i really need this to work soon and i'm sorta stuck in a mental rut...

Thanks in advance for any assistance!!!!

this is what i have so far: 
use DBI;

###### connect to the database

my ($dbh,$sth,$count);  
$dbh = DBI->connect ("DBI:mysql:host=boognish;database=benchwarmer",
     "joda","waliays",
        {PrintError => 0, RaiseError => 1});

###### name, input, and read the text into an array

$input_file = 'RUN-mysql-win2k.txt';
open(INFO, $input_file); 
@input = <INFO>;  

###### slice the array input into six element chunks

@input[0,1,2,3,4,5]=@single_line 

#assign values to the various parts of the single_line array

$operation=$single_line[0]; 
$seconds=$single_line[1]; 
$usr=$single_line[2];   
$sys=$single_line[3];   
$cpu=$single_line[4];     
$tests=$single_line[5];   

sub write_data {
my($operation,$seconds,$usr,$sys,$cpu,$tests) = @_;
$count = 0;
$count += $dbh->do ("INSERT INTO main (operations,seconds,usr,sys,cpu,
+tests)
VALUES($operation,$seconds,$usr,$sys,$cpu,$tests)

[download]

Replies are listed 'Best First'.
Re: I Am Lame- trying to process some text...
by dws (Chancellor) on Feb 02, 2002 at 22:37 UTC
    It's as easy to process a text file line-by-line as it is to suck it into an array first. And line-by-line is more memory friendly. You'll find examples of the line-by-line idiom in nearly every Perl book you may chance to pick up.

    Try something like this (and note the extra error handling): 

      my $sth = $dbh->prepare(<<"SQL");
  INSERT INTO main (operations,seconds,usr,sys,cpu,tests)
  VALUE (?,?,?,?,?,?)
  SQL

  open(IN, "<$input_file") or die "$input_file: $!";
  while ( <IN> ) {
      chomp;        # remove the trailing \n
      my($operation, $seconds, $usr, $sys, $cpu, $tests) =
          split;

      $sth->execute($operation,$seconds,$usr,$sys,$cpu,$tests);
  }

  close(IN);

    [download]
    To get SQL errors reported, use {RaiseError => 1} when you make the connection. See the DBI doc for details.

[reply]
[d/l]
[select]
Re: I Am Lame- trying to process some text...
by Zaxo (Archbishop) on Feb 02, 2002 at 23:12 UTC

    You're not splitting the lines up into an array. If I read your data correctly, split " "; will do that. I'd also recommend reading one line at a time from the flat file, and recommend a prepared $sth with placeholders. Something like this (I'll go ahead and use the password you gave, since you will change it, won't you? ):

    #!/usr/bin/perl -w
use strict;
use DBI;

my ($dbh,$sth,$count);        
$dbh = DBI->connect ("DBI:mysql:host=boognish;database=benchwarmer",
             "joda","waliays",
               {PrintError => 0, RaiseError => 1});
$sth = $dbi->prepare('INSERT INTO main (operations,seconds,usr,sys,cpu
+,tests)
                        VALUES (?,?,?,?,?,?)');

my $input_file = '/path/to/RUN-mysql-win2k.txt';

open(INFO, "< $input_file") or die $!;
while (<INFO>) {
    # does parse to list and write to db in one swell foop
    $sth->execute( split " " );
}
close INFO or die $!;
$sth->finish;
$dbi->disconnect;

    [download]
    In the read loop, split is working on it's default argument, $_, which is provided by while. That parsing assumes data has no whitespace and whitespace of some kind is the field delimeter. I've also added touches like warnings, strict and error checking. Those will assist you if you use them.

    Update: Added finish and disconnect, corrected a typo.

    After Compline,
    Zaxo

[reply]
[d/l]

Back to Seekers of Perl Wisdom