puting an excel file into a perl data structure

[KaZ]

Hello,

I have a script which make a lot of searches into an excel file. I use
Spreadsheet:arseExcel to read it everytime but it is slow. So I want
to (thanks to Peter J. Holzer) put it into a perl data structure. I
tried this:

------------------
#! /usr/bin/perl

use strict;
use warnings;
use Spreadsheet:arseExcel;

my $DB_oWkS =
Spreadsheet:arseExcel::Workbook->Parse('/path/to/ExcelFile')->{Worksheet}[0];

my %bts_db;

for (my $iR = 2 ; (defined $DB_oWkS->{MaxRow}) && ($iR <=
$DB_oWkS->{MaxRow}) ; $iR++) {
my $first = $DB_oWkS->{Cells}[$iR][0]->Value;
my $attr1 = $DB_oWkS->{Cells}[$iR][1]->Value;
my $attr2 = $DB_oWkS->{Cells}[$iR][4]->Value;
my $attr3 = $DB_oWkS->{Cells}[$iR][6]->Value;
my $attr4 = $DB_oWkS->{Cells}[$iR][7]->Value;
$bts_db{$first}{attr1} = $attr1;
$bts_db{$first}{attr2} = $attr2;
$bts_db{$first}{attr3} = $attr3;
$bts_db{$first}{attr4} = $attr4;
}
---------

To search for a "first" value, I do, for example:

if (exists $bts_db{"myValue"}) { return $bts_db{"myValue"}{attr2}; }
else { return 0; }

Now I want to be able to check if a particular attr1 value exists,
whatever the key value is. How do I best do this? Whith a loop? Or is
there any other solution?

BTW, I'd like to know how you call this kind of hash. Is it a hash of
hashes? (Yes I'm a newbie to perl)

 

[Peter J. Holzer]

I have a script which make a lot of searches into an excel file. I use
Spreadsheet:arseExcel to read it everytime but it is slow. So I want
to (thanks to Peter J. Holzer) put it into a perl data structure. I
tried this:

------------------
#! /usr/bin/perl

use strict;
use warnings;
use Spreadsheet:arseExcel;

my $DB_oWkS =
Spreadsheet:arseExcel::Workbook->Parse('/path/to/ExcelFile')->{Worksheet}[0];

my %bts_db;

my %attr1_seen;

for (my $iR = 2 ; (defined $DB_oWkS->{MaxRow}) && ($iR <=
$DB_oWkS->{MaxRow}) ; $iR++) {
my $first = $DB_oWkS->{Cells}[$iR][0]->Value;
my $attr1 = $DB_oWkS->{Cells}[$iR][1]->Value;
my $attr2 = $DB_oWkS->{Cells}[$iR][4]->Value;
$bts_db{$first}{attr1} = $attr1;
$bts_db{$first}{attr2} = $attr2;

$attr1_seen{$attr1} = 1;

}
---------

To search for a "first" value, I do, for example:

if (exists $bts_db{"myValue"}) { return $bts_db{"myValue"}{attr2}; }
else { return 0; }

Now I want to be able to check if a particular attr1 value exists,
whatever the key value is. How do I best do this? Whith a loop?

That's a possibility, but in general not the fastest one.

Or is there any other solution?

See above. You use a different hash with $attr1 as the key. Then you can
simply use

if ($attr1_seen{'myAttr'}) { ... }

if you need to know which key(s) have a specific attr1 value, you can
store the keys instead of 1:

push @{$attr1_seen{$attr1}}, $first;

hp