How to read/write this data.

[Justin C]

I have ten Excel files which I read with Spreadsheet:arseExcel. 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.

 

[John Bokma]

$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:umper;
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;

 

[cartercc]

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]

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

 

[Tad J McClellan]

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.

 

[Justin C]

I have ten Excel files which I read with Spreadsheet:arseExcel. 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:umper, 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.