How to read/write this data.
Justin C
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.
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::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
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
Glenn Jackman
> On Sep 4, 10:45 am, Justin C <justin.0...@purestblue.com> 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}}})
Mind your braces: should be:
foreach my $row (sort keys %{ $data{$file}{$section} })
> { print "$row\t"; #sanity check
> foreach my $col (sort keys %{$data{$file{$section{$row}}}})
should be:
foreach my $col (sort keys %{ $data{$file}{$section}{$row} })
> { print "$col\t"; #sanity check
> print "\nDATA: $data{$file}{$section}{$row}{$col}";
That will print something like "DATA: HASH(0x100d8b08)". You need
another layer to iterate over the keys of %$col:
my $ref = $data{$file}{$section}{$row}{$col};
while (my ($key, $val) = each %$ref) {
print "'$key' => '$val'\n";
}
I too frequently use Data::Dumper with $Data::Dumper::Indent--;
--
Glenn Jackman
Write a wise saying and your name will live forever. -- Anonymous
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.
--
Tad McClellan
email: perl -le "print scalar reverse qq/moc.noitatibaher\100cmdat/"
Justin C
>
> 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.
Glenn Jackman
> On 2008-09-04, Justin C <justi...@purestblue.com> wrote:
> > $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:!!!
Data::Dumper takes a scalar argument, so if you want to pass the %data
hash, you:
print Dumper(\%data)