Re^4: Direct to spreadsheet

Actually, after your reply I used my (very) limited Perl knowledge to change things a bit, based in part on what you replied. I am still far from happy with my code and your (and other monks) help is much appreciated.
What I really want to do is amalgamate what I was doing with the intermediate file processing into a single more efficient and compact loop. My code now looks like this

# Create the spreadsheet and set up formats
$workbook  = Spreadsheet::WriteExcel->new("my.xls");
# Set up various workbook formats here....
# Create some worksheets
         $sheet1 = $workbook->add_worksheet('Tools');
         $sheet2 = $workbook->add_worksheet('Client');
         $sheet3 = $workbook->add_worksheet('Fares');
# create some column headings for each worksheet here...

# Set up some lookup lists using Regexp::List here...

# execute the select statement
my @data2;
$sth=$dbh->prepare($sqlcmd);
$sth->execute;
$sth->bind_columns ( \( $sp, $ev, $seq, $log, $db, $ex) );
while ($sth->fetchrow_arrayref) {
   chomp $ex;
   $ex =~ tr/\t//; # get rid of the tabs in the ex part of the record
      if ( $seq > 1) {
         push @data2, "$ex";
      } else {
         push @data2, "\nBOR $ev|$sp|$log|$db|$seq|$ex";
      }
}
$sth->finish;
$dbh->disconnect;

$/="BOR ";
for (@data2) {
    chomp;
#    if( m[/\-] ) {
#       $_ .= <TXT> until m[\-/];
#      s[ \s? / \- .+? \- / \s? ][]smgx;# cut out some unwanted txt
#     chomp;
#}
   my @rec = split /\|/;
      $aa = $rec[0];
      $bb = $rec[1];
      $cc = $rec[2];
      $dd = $rec[3];
      # $rec[4] isn't used here
      $ee = $rec[5];

    if ($ee =~ /$regexp1/) {
       popxls($sheet1,$normal,$darow); # populate worksheet 1
       $darow++;
    } elsif ($ee =~ /$regexp2/) {
       popxls($sheet2,$normal,$arow); # populate worksheet 2
       $arow++;
    } elsif ($ee =~ /$regexp3/) {
       popxls($sheet3,$normal,$srow); # populate worksheet3
       $srow++;
    }
}
[download]

I had to comment out the bit where I'm getting rid of unwanted text because I'm not sure how to handle this now I'm reading from an array rather than a filehandle. I'd really like to reinstate this
I don't like my code because it would be more efficient if I didn't push the entire dataset into another array (@data2) and then loop through that before writing to the spreadhseet.
I can't figure out how to amalgamate the two loops into one so that I process each record rather than the whole dataset ?

Comment on Re^4: Direct to spreadsheet Download Code

Replies are listed 'Best First'.
Re^5: Direct to spreadsheet by McDarren (Abbot) on Jan 23, 2006 at 17:22 UTC
Okay, well I'm guessing that what you are trying to do is remove any comments from some code, where comments are enclosed with \- and -/. If that's the case, something like this should do it: `for (@data) { chomp; $_ =~ s\| \\- # Start of a commented section [^(-/)]+ # Anything that isn't the end of a comment -/ # End of commented section \|\|gx; }` [download] However, I suspect that you'll still have problems. In your original code you were setting the input record separator ($/) to "BOR". In your current code, this really has no effect as you're no longer reading from a filehandle. And so the "/nBOR" that you're inserting into each @data element is no longer having the effect you think it is. I guess you need to do all your processing as you iterate through the SQL result set. But even then, you're building a list where each element is basically another list, and then later splitting the inner list out. I think you really should think about building a LoL in the first place (or perhaps a LoH). You could then get rid of that `$aa = $rec[0]; $bb = $rec[1];` nonsense ;) Cheers, Darren :)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^5: Direct to spreadsheet
by McDarren (Abbot) on Jan 23, 2006 at 17:22 UTC

If that's the case, something like this should do it:

for (@data) {
    chomp;
    $_ =~ s|
        \\-         # Start of a commented section
        [^(-/)]+    # Anything that isn't the end of a comment
        -/          # End of commented section
        ||gx;
}
[download]

However, I suspect that you'll still have problems. In your original code you were setting the input record separator ($/) to "BOR". In your current code, this really has no effect as you're no longer reading from a filehandle. And so the "/nBOR" that you're inserting into each @data element is no longer having the effect you think it is. I guess you need to do all your processing as you iterate through the SQL result set.

But even then, you're building a list where each element is basically another list, and then later splitting the inner list out. I think you really should think about building a LoL in the first place (or perhaps a LoH). You could then get rid of that $aa = $rec[0]; $bb = $rec[1]; nonsense ;)

Cheers,
Darren :)

[reply]
[d/l]
[select]