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

All, I am trying to use the Data::Pivot on data from a query to load it to excel. I load data from my querys a lot using an array reference. 

foreach $stmt_from_query (@{$array_ref}) 
{ 
   $worksheet->write_col(1,0,$array_ref,$format1); 
   last;
}

[download]

However, with the Data-Pivot mod you have to load the data into an array. Once I have the data in an array, I can assign the pivoted data to a no array and then load it to excel. 

use Data::Pivot;
  @newtable = pivot( table        => \@table,
                     headings     => \@headings,
                     pivot_column => $pivot_col_no,
                     layout       => 'vertical',
                     row_sum      => 'Sum',
                     row_titles   => 1,
                     format       => '%5.2f',
                   )

[download]

I have tried to assign the data in the query to an array. Can someone help me with the syntax to assign the data from an array reference to an array and then use some kind of loop to read the data to a spreadsheet?

Replies are listed 'Best First'.
Re: Query Data for Data-Pivot
by jethro (Monsignor) on Nov 19, 2009 at 22:07 UTC

    I have some problems understanding your question. It seems you have your data in an array, i.e. @{$array_ref}. Note you can use this dereferenced array reference anywhere you need an array

    Now Data::Pivot seems to need an Array of Arrays (often called simply AoA) for @table. And you probably have the data just in a simple array. Is that what you are getting at? In that case the following code would work (provided that you stored the number of columns into $columns):

    my @table=();
my $row=0;
while (@{$array_ref}) {
  for (1..$columns) {
    push @{$table[$row]}, shift @{$array_ref};
  }
  $row++;
}

    [download]

    Now you can call pivot. To get the data into a simple array again you can use this:

    my @table_linear;
map { push @table_linear, @{$_} }, @table;

    [download]

    or, if you prefer loops:

    my @table_linear;
foreach (@table) { 
  push @table_linear, @{$_} 
}

    [download]

    If that isn't the answer to your question, please provide examples

[reply]
[d/l]
[select]

      I appreciate you help. I was able to get it to work. I was also able to create an array using the following: 

      foreach $i(0..$#{$a_row})
{
   foreach $j (0..$#{$a_row->[$i]} ) 
   {
    $array[$i][$j] = $a_row->[$i][$j];
    }
}

print Dumper(\@array);

      [download]

      I don't like the Data-Pivot module. It is not pivoting my dates to be the column headers like I want. So, I am going to try Win32::OLE. I just don't know if I can feed the data to a spreadsheet without specifing the range of the spreadsheet. With Spreadsheet::WriteExcel it loads the data to the end of file. All of the examples of Win32::OLE are asking for a range. I am trying to load data from a query where I don't know the range. Anyway, I appreciate you help.

[reply]
[d/l]

Back to Seekers of Perl Wisdom