in reply to Re^2: Direct to spreadsheet
in thread Direct to spreadsheet

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 :)

Replies are listed 'Best First'.
Re^4: Direct to spreadsheet
by Anonymous Monk on Jan 23, 2006 at 16:03 UTC
    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]
      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]

In Section Seekers of Perl Wisdom