in reply to Direct to spreadsheet

Well, to answer your question first (and it's a little bit difficult without seeing some sample data) - I'd probably suggest using something like selectall_hashref and load all of your data into a hashref. You could then write directly to the Excel worksheets.

A few comments about your code: 

# execute the sql statement
$sth=$dbh->prepare("@sql");

[download]
... looks a little odd to me. It seems to suggest that you are passing a list of statements, rather than a single one?

Also, you appear to be selecting at least 14 rows of data, yet you only use 6 of them. What is the point of that? Are you doing select * from ... or something?

I strongly suspect that your code could be significantly simplified by improving your SQL select statement to only return the data that you actually need to use. Perhaps you could post that along with a sample set of data?

Cheers,
Darren :)

Update: One thing that I forgot to mention is that your choice of variable names makes your code a little difficult to follow. Stuff like $aa = $rec[0]; doesn't really tell you much about the data. It's a personal thing, but I usually try to make my variable names as descriptive as possible - makes it easier for the next person who comes along and has to debug/maintain my code (usually me) :)

That's one reason why I like to use selectall_hashref on the results of my DB queries. I can then refer to the data in a very descriptive way.

Replies are listed 'Best First'.
Re^2: Direct to spreadsheet
by Anonymous Monk on Jan 23, 2006 at 11:40 UTC
    Thanks for the comments. I'd like to answer your queries if I may.
    The @sql is used because a sql string which consists of many parts including a "select * from" is built further up the code. The sql has been tuned for maximum efficiency, it's the Perl bit which hasn't. The code shown is a small subsection of the whole story. Apologies if I didn't make this clear. This is my first post on this site
    I don't use  selectall_hashref because I found that for larger datasets this significantly reduces the performance of returning the dataset required. I appreciate that cosmetically the code is much more readable using the  selectall_hashref and for smaller datasets I do use this.
[reply]
[d/l]
[select]
      The @sql is used because a sql string which consists of many parts including a "select * from" is built further up the code.

      Okay, but it still doesn't make sense. $sth->prepare accepts just a single parameter (an SQL statement string). So as far as I can see, the only way your code can possibly work is if you are passing a single element list. In which case, you should be assigning your SQL string to a scalar variable (ie. $sql).

      However, that doesn't really address your current problem. If reading your entire result set into a hashref isn't an option, then you probably want a LoL

      In any case, your current use of selectall_arrayref fetchrow_arrayref is un-necessary. You could simply do something like: 

      while (@row = $sth->fetchrow_array) {

      [download]
      ...and then the line 
      print TXT  "\nBOR $data->[3]|$data->[2]|$data->[9]|$data->[10]|$data->
+[4]|$data->[13]";

      [download]
      ..simply becomes 
      print TXT "\nBOR ", join("|", @data[3,2,9,10,4,13]);

      [download]
      ..or as GrandFather suggested 
      push @data, "\nBOR ", join("|", @data[3,2,9,10,4,13]);

      [download]

      Apologies if I'm not appearing very helpful. But as I said earlier, I'm finding it difficult to visualise the solution without actually seeing what your data looks like. Perhaps some more experienced monk will chip in and offer their advice :)

      Cheers,
      Darren :)

[reply]
[d/l]
[select]
        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 ?
[reply]
[d/l]

In Section Seekers of Perl Wisdom