How to read/write this data.

Discussion in 'Perl Misc' started by Justin C, Sep 4, 2008.

  1. Justin C

    Justin C Guest

    I have ten Excel files which I read with Spreadsheet::parseExcel. Each
    file has only one worksheet. The worksheets are 6 columns by
    $sheet->MaxRow (ie, I don't know, but perl can find out). Each sheet is
    divided into sections of identically structured data, an empty line
    divides the sections.

    I want to access the data, so it can be output identically, but in a
    different format (I can't do straight read/write because I need to do
    some calculations on line numbers for page-break purposes before I
    output). My intention was to read it all into a hash whose keys would be
    the file name, and data would be a hash, whose keys would be a section
    number, and data would be a hash.... etc all the way down to cell
    contents.

    I end up with something like this:

    $data{$file}{$section}{$row}{$col}{data} = $cell->{Val}
    $data{$file}{$section}{$row}{$col}{format} = $cell->{Format}

    I have a hash, $deity knows what's in it! I can't figure out how to
    work through it. For example, I need to know how many sections there are
    in each file, and how many rows in each section. For the very bottom end
    of the processing I need to iterate over the data and format of each
    column of each row in each section.

    I'm hopelessly lost!

    Looking at some docs, I find that what I have (or should have) is:
    a hash of file names containing
    an array of sections containing
    an array of rows containing
    an array of columns containing
    a hash of value/format data

    I've been looking at the chapter 4.7 of Programming Perl, data structure
    code examples. There is a "composition of more elaborate records"
    section, but I can't translate that to what I have, my brain is just not
    getting it.

    Maybe I should ask something simple to start with: How do I get the
    number of sections that are in, say, file number 2 ($file == 2)?

    Actually, I'm not even certain I've got the hash right.

    Pointers to reading matter, explanations, suggestions, anything (even a
    gun and a bullet) will be gratefully received.

    Justin.

    --
    Justin C, by the sea.
    Justin C, Sep 4, 2008
    #1
    1. Advertising

  2. Justin C

    John Bokma Guest

    Justin C <> wrote:


    > $data{$file}{$section}{$row}{$col}{data} = $cell->{Val}
    > $data{$file}{$section}{$row}{$col}{format} = $cell->{Format}
    >
    > I have a hash, $deity knows what's in it! I can't figure out how to
    > work through it.


    use Data::Dumper;
    print Dumper $data;

    (or use YAML)


    > For example, I need to know how many sections there are
    > in each file


    my $number_of_sections = keys %{ $data{ $file } }

    $data{ $file } contains a reference to a hash of sections.

    [..]
    > Looking at some docs, I find that what I have (or should have) is:
    > a hash of file names containing
    > an array of sections containing
    > an array of rows containing
    > an array of columns containing
    > a hash of value/format data


    You have a hash of hash of .. etc.

    What you describe is:

    $data{ $file }[ $section ][ $row ][ $col ]{

    data => $cell->{ Val },
    format => $cell->{ Format },
    };

    > Pointers to reading matter, explanations,


    {} = hash
    [] = array

    Use Data Dumper to examine your data structure(s).

    Remeber that if you're doing stuff with a section, you can make your
    code more readable as follows:

    my $current_section = [];

    $current_section->[ $row ][ $col ]{ data } ...

    $data{ $file }[ $section ] = $current_section;


    --
    John http://johnbokma.com/ - Hacking & Hiking in Mexico

    Perl help in exchange for a gift:
    http://johnbokma.com/perl/help-in-exchange-for-a-gift.html
    John Bokma, Sep 4, 2008
    #2
    1. Advertising

  3. Justin C

    cartercc Guest

    On Sep 4, 10:45 am, Justin C <> wrote:
    > I end up with something like this:
    >
    > $data{$file}{$section}{$row}{$col}{data} = $cell->{Val}
    > $data{$file}{$section}{$row}{$col}{format} = $cell->{Format}


    You can iterate through a complex structure of hashes like this:

    foreach my $file (sort keys %data)
    { print "\n$file\t"; #sanity check
    foreach my $section (sort keys %{$data{$file}})
    { print "$section\t"; #sanity check
    foreach my $row (sort keys %{$data{$file{$section}}})
    { print "$row\t"; #sanity check
    foreach my $col (sort keys %
    {$data{$file{$section{$row}}}})
    { print "$col\t"; #sanity check
    print "\nDATA: $data{$file}{$section}{$row}{$col}";
    }
    }
    }
    }

    The little book about Perl Objects, References, and Modules (Schwartz)
    is good.

    CC
    cartercc, Sep 4, 2008
    #3
  4. Justin C

    cartercc Guest

    On Sep 4, 2:59 pm, Glenn Jackman <> wrote:
    > Mind your braces: should be:
    >            foreach my $row (sort keys %{ $data{$file}{$section} })


    Yeah. I bashed this out from memory. Normally it would take me several
    tries to get it right -- I always have problems with the braces but
    it's not too hard to correct as long as you understand how the
    references work.

    CC
    cartercc, Sep 4, 2008
    #4
  5. Justin C <> wrote:

    > I end up with something like this:
    >
    > $data{$file}{$section}{$row}{$col}{data} = $cell->{Val}


    > I have a hash, $deity knows what's in it! I can't figure out how to
    > work through it.



    See

    perldoc perlreftut

    then apply "Use Rule 1", which I like to do in 3 steps.


    > For example, I need to know how many sections there are
    > in each file,



    1) pretend it is a plain old hash, using the scalar value of keys():

    my $section_count = keys %hash;

    2) replace the hash name with a block:

    my $section_count = keys %{ };

    3) fill in the block with something that returns the right
    kind of reference (to a hash in this case):

    my $section_count = keys %{ $data{$file} };


    > Looking at some docs, I find that what I have (or should have) is:
    > a hash of file names containing
    > an array of sections containing
    > an array of rows containing
    > an array of columns containing
    > a hash of value/format data



    > Maybe I should ask something simple to start with: How do I get the
    > number of sections that are in, say, file number 2 ($file == 2)?


    my $section_count = keys %{ $data{2} };

    > Actually, I'm not even certain I've got the hash right.



    It surely does not match your word description...


    > Pointers to reading matter,



    perlreftut dude.


    --
    Tad McClellan
    email: perl -le "print scalar reverse qq/moc.noitatibaher\100cmdat/"
    Tad J McClellan, Sep 5, 2008
    #5
  6. Justin C

    Justin C Guest

    On 2008-09-04, Justin C <> wrote:
    >
    > I have ten Excel files which I read with Spreadsheet::parseExcel. Each
    > file has only one worksheet. The worksheets are 6 columns by
    > $sheet->MaxRow (ie, I don't know, but perl can find out). Each sheet is
    > divided into sections of identically structured data, an empty line
    > divides the sections.
    >
    > I want to access the data, so it can be output identically, but in a
    > different format (I can't do straight read/write because I need to do
    > some calculations on line numbers for page-break purposes before I
    > output). My intention was to read it all into a hash whose keys would be
    > the file name, and data would be a hash, whose keys would be a section
    > number, and data would be a hash.... etc all the way down to cell
    > contents.
    >
    > I end up with something like this:
    >
    > $data{$file}{$section}{$row}{$col}{data} = $cell->{Val}
    > $data{$file}{$section}{$row}{$col}{format} = $cell->{Format}


    Thank you to all who replied. Lots to read, can't take it all in yet.

    I did a quick and dirty Data::Dumper, as per the docs, $VAR1 = undef:!!!

    Well, I'm certainly not doing something right, but I'll definitely give
    perlreftut a good read (again - the first time through was quite a while
    ago, and I don't think it went in then).

    Anyway, thanks again, no doubt I'll be back at the next hurdle.

    Justin.

    --
    Justin C, by the sea.
    Justin C, Sep 5, 2008
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Isaac
    Replies:
    0
    Views:
    2,458
    Isaac
    Jul 10, 2003
  2. sweety
    Replies:
    9
    Views:
    1,017
    Richard Heathfield
    Feb 7, 2006
  3. Trans
    Replies:
    3
    Views:
    167
    Trans
    Dec 21, 2005
  4. sahm
    Replies:
    4
    Views:
    679
  5. Tim Chase
    Replies:
    0
    Views:
    88
    Tim Chase
    Dec 16, 2013
Loading...

Share This Page