puting an excel file into a perl data structure

K

KaZ

Hello,

I have a script which make a lot of searches into an excel file. I use
Spreadsheet::parseExcel 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::parseExcel;

my $DB_oWkS =
Spreadsheet::parseExcel::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)
 
P

Peter J. Holzer

KaZ said:
I have a script which make a lot of searches into an excel file. I use
Spreadsheet::parseExcel 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::parseExcel;

my $DB_oWkS =
Spreadsheet::parseExcel::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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top