Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Using Array of Arrays

by Aim9b (Monk)
on Sep 19, 2007 at 19:29 UTC ( #639977=perlquestion: print w/replies, xml ) Need Help??

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

In the TPJ#9 there is a sample of loading an xls file from a text file. I can load it, but all rows are the same- the first record. I think the problem is in the way I populate the array. Can someone steer me back on track? Thanks.

The sample code says -
my $Excel = Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{SheetsInNewWorkbook} = 1; my $Book = $Excel->Workbooks->Add; my $Sheet = $Book->Worksheets(1); $Sheet->{Name} = 'Candle'; # Insert column titles my $Range = $Sheet->Range("A1:E1"); $Range->{Value} = [qw(Time Open High Low Close)]; $Range->Font->{Bold} = 1; $Sheet->Columns("A:A")->{NumberFormat} = "h:mm"; # Open/High/Low/Close to be displayed in 32nds $Sheet->Columns("B:E")->{NumberFormat} = "# ?/32"; # Add 15 minute data to spreadsheet print "Add data\n"; $Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars); $Range->{Value} = \@Bars;
The last statement shows how to pass arrays to OLE objects. The Win32::OLE module automatically translates each array reference to a SAFEARRAY, the internal OLE array data type. This translation first determines the maximum nesting level used by the Perl array, and then creates a SAFEARRAY of the same dimension. The @Bars array already contains the data in the correct form for the spreadsheet:
(Time1, Open1, High1, Low1, Close1,
... TimeN, OpenN, HighN, LowN, CloseN)

What I don't understand is HOW the data gets into the @Bars array. It looks like its an Array of Arrays, but I'm confused as to how to generate it. If my code reads a line, should I split it at the commas into my @fields, & then push the @fields entry onto my @rows array.
Here's my code.
while ($inBuf = <csvFILE>) { chomp($inBuf); ++$line_count; $inBuf =~ s/^\"//; # Take out any LEADING or $inBuf =~ s/\"$//; # TRAILING double quotes # OK, Process this record @fields = split(/\,/,$inBuf); print "\nProcessing record $fields[0]"; push @rows, @fields; ++$item_count; } # End of csvFILE or we reached our Runaway count close csvFILE;

Replies are listed 'Best First'.
Re: Using Array of Arrays
by FunkyMonk (Chancellor) on Sep 19, 2007 at 19:35 UTC
    push @rows, @fields just pushes all the elements of @fields on to @rows

    What you need to do is push an arrayref on to @rows:

    push @rows, \@fields

    perldsc has much more to say about Perl data structures

    update: For the above to work, you should be declaring @fields using my (which is best practice anyway).

Re: Using Array of Arrays
by GrandFather (Saint) on Sep 19, 2007 at 21:00 UTC

    As an aside, it is strongly recommended that you declare variables as close to the point they are used as possible. Generally that means at the place where the first assignment is made to them. Consider:

    while (my $inBuf = <csvFILE>) { chomp($inBuf); $inBuf =~ s/^\"//; # Take out any LEADING or $inBuf =~ s/\"$//; # TRAILING double quotes # OK, Process this record my @fields = split(/\,/,$inBuf); print "\nProcessing record $fields[0]"; push @rows, \@fields; # Can push \@fields because @fields is local t +o the loop } # End of csvFILE or we reached our Runaway count close csvFILE;

    Note that the duplicated counters have gone. scalar @rows gives you the line count in any case. If you need an item count then the correct code would be:

    my $items = 0; while (...) { ... $items += @fields; # Array in scalar context gives number of elemen +ts }

    I presume you use strictures (use strict; use warnings;. If not you ought, always!

    Also, parsing csv can be tricky. What will happen if your code meets the following line for example?

    1,"This, or this and that",3

    The better solution is to use one of the modules designed for the purpose such as Text::CSV.

    DWIM is Perl's answer to Gödel
      GrandFather, thanks for the tips. Yes I use strict and warnings. Also, I'd not heard of the declare when used (my) before, so I've been declaring all variables at the top of my program. Its a pain going back & forth, but I think it will help the next person, to see all fields at the outset. Right after my 'use' statements, I put the a string of  my $fieldname = 0  # What it's used for
      sort of as a quick ref for the next person...even if it's me.
      If I encounter the 1,"This... that you showed, I expect my array will just have 3 elements instead of the normal 10, and when I load the spreadsheet (via Win32::OLE), the other 7 cells in that row will be blank or 0.
      I'll try the Text::CSV, but I wanted to learn the raw "under the covers" way to do it first... it's a character flaw. ;-) but I think I then understand better what's happening.
      Thanks again. You folks have been a super help during this learning curve.

        There is a piece of common wisdom that says that you can remember about seven things at a time in your short term memory (Human register set I guess). So, given that limitation there is no reason at all to declare all your variables in one place at the start of anything - you'll forget them anyway.

        However, the much more important reason for declaring a variable as late as possible is that it is much easier to see where and how it is used. There is no need to know about a variable until the variable is needed so there is no need to burden the limited storage facility of the reader of your code by introducing variables before they are needed. Bottom line: Declaring variables early doesn't help anyone.

        There is nothing wrong with learning about the 'raw "under the covers" way to do it', but there are some tasks such as parsing generally, and parsing markup in particular that are much more subtle than one might first think. It is well worth being aware of the modules that are available for performing such tasks after your initial foray into rolling your own.

        Perl is environmentally friendly - it saves trees
        If I encounter the 1,"This... that you showed, I expect my array will just have 3 elements instead of the normal 10

        1,"This, or this and that",3 will result in 4 (not 3) entries if you use a simple split. The important point is that split will not distinguish between the comma in the quoted string and a comma used to separate fields.

        If your data contains no strings then you won't have a problem. If it does contain strings you may encounter some rather subtle bugs including silently producing incorrect answers.

        Perl is environmentally friendly - it saves trees
Re: Using Array of Arrays
by bruceb3 (Pilgrim) on Sep 19, 2007 at 19:38 UTC
    The line;
    push @rows, @fields;
    Needs to be;
    push @rows, [ @fields ];
    Here is an example;
    #!/usr/bin/env perl use strict; use warnings; use Data::Dumper; my @fields = qw/ fred one two three /; my @out; push @out, [ @fields ] for ( 1..3 ); print Dumper \@out;
    Which produces the output;
    $VAR1 = [ [ 'fred', 'one', 'two', 'three' ], [ 'fred', 'one', 'two', 'three' ], [ 'fred', 'one', 'two', 'three' ] ];
Re: Using Array of Arrays
by Aim9b (Monk) on Sep 19, 2007 at 20:23 UTC
    Thanks Monks. The \@fields seemed to solve the problem. Bruceb3, the [@fields] ran, but still gave me all the same first record. Is there a prefference to pushing an entire array, vs. just a ref to it? I'll check out the perldoc & study a little further. Also, I assume the 2+$#Bars in the sample code above, is because she doesn't know how many rows she'll need, correct? Thanks again for the help.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://639977]
Approved by FunkyMonk
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2021-06-22 17:30 GMT
Find Nodes?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)

    Results (108 votes). Check out past polls.