Thanks Shlomi! I apologize for not using your suggestion. The module Yuval was pointing me to, implemented exactly what I needed and it seemed to be simpler to use. And indeed it is executing in a flash! May be your solution might be even faster, so I'll check it later and report.
Regards, Meir -----Original Message----- From: Shlomi Fish [mailto:[email protected]] Sent: Thursday, July 22, 2010 12:58 PM To: [email protected] Cc: Meir Guttman Subject: Re: [Israel.pm] Constructing a transposed 2D array reference using multiple single-dimension array references On Thursday 22 Jul 2010 12:45:02 Meir Guttman wrote: > Hello all Perl savvy folks! > > > > This is very much a newbie question, but I am sorry to say that I couldn't > find an answer to it on the net. > > > > My application is to query a relational DB and put the resulting output > table, TRANSPOSED, into an Excel "range". > > > > Doing it one-cell-at-a-time using Win32::OLE's (Actually MS VBA's) > "Cells()" method is easy, but excruciatingly slow. A much faster way is to > use OLE's "Range()" method. This method, as implemented in the WIN32::OLE > package expects a _reference_ to a 2D Perl array. (An Excel "range" such > as "B5:E17" is in general a 2D object.) > > > > From the DBI package I can fetch one row at a time and get either an array > or an array-reference (there are other possibilities, but these two seem to > be the most relevant): > > > > * @array = $sth->fetchrow_array; > > * $array_ref = $sth->fetchrow_arrayref; > > > > The second is stated as the fastest way, which makes sense. Rather than > copying the whole array, just pass back a single reference. > > > > Is there a way by which I can _directly_ use the above 1D DB-row > array-references (plural...) to pass a single reference to a transposed 2D > array, structured as follows: > > > > $array_2D_ref = > > [ [row0_elem0, row1_elem0, ..., rowj_elem0], > > [row0_elem1, row1_elem1, ..., rowj_elem1], > > [... ], > > [... ], > > [row0_elemi, row1_elemi, ..., rowj_elemi] ]; > You can use a map: <<< #!/usr/bin/perl use strict; use warnings; use Data::Dumper; my $a_ref = [[1,2,3],[4,5,6],[7,8,9]]; my $transposed = [ map { my $x = $_; [map { $_->[$x] } @$a_ref]} (0 .. $#{$a_ref->[0]}) ]; print Dumper($transposed); >>> There may be a better way to do it using one of the List:: modules (List::Util, List::MoreUtils, List::By, etc.) and naturally , there's http://pdl.perl.org/ . Regards, Shlomi Fish P.S: hope to see you in today's Tel Aviv Open Source Club meeting: http://wiki.osdc.org.il/index.php/Tel_Aviv_Meeting_on_22_July_2010 -- ----------------------------------------------------------------- Shlomi Fish http://www.shlomifish.org/ The Case for File Swapping - http://shlom.in/file-swap God considered inflicting XSLT as the tenth plague of Egypt, but then decided against it because he thought it would be too evil. Please reply to list if it's a mailing list post - http://shlom.in/reply . _______________________________________________ Perl mailing list [email protected] http://mail.perl.org.il/mailman/listinfo/perl
