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 ?

In reply to Re^4: Direct to spreadsheet by Anonymous Monk
in thread Direct to spreadsheet by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.