srins has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
I need to parse inputs from excel file and populate in to an oracle database automatically. I used Spreadsheet::ParseExcel; from cpan,and able to parse from my excel sheet 

#!/opt/perl5.8/bin/perl
#package Spreadsheet::ParseExcel::Simple;

use strict;
use Spreadsheet::ParseExcel;
use Data::Dumper;
#use Spreadsheet::ParseExcel::Simple;

#use strict;
#    use Spreadsheet::ParseExcel;
    my $oExcel = new Spreadsheet::ParseExcel;

    #1.1 Normal Excel97
    my $oBook = $oExcel->Parse('test1.xls');
    my($iR, $iC, $oWkS, $oWkC);
    print "FILE  :", $oBook->{File} , "\n";
    print "COUNT :", $oBook->{SheetCount} , "\n";
    print "AUTHOR:", $oBook->{Author} , "\n";
    for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
        $oWkS = $oBook->{Worksheet}[$iSheet];
        print "--------- SHEET:", $oWkS->{Name}, "\n";
        for(my $iR = $oWkS->{MinRow} ;
                defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $i
+R++) {
            for(my $iC = $oWkS->{MinCol} ;
                            defined $oWkS->{MaxCol} && $iC <= $oWkS->{
+MaxCol} ; $iC++) {
                $oWkC = $oWkS->{Cells}[$iR][$iC];
              #  $oWkC = $oWkS->{Cells}[$iR][0];
                print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC)
+;  # Formatted Value
#
                #print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC
+);  # Formatted Value
#                print "( $iR , $iC ) =>", $oWkC->{Val}, "\n" if($oWkC
+);  # Original Value
            }
        }
    }

[download]
my excel input will be in format 
component	mode		command
comp1		mode1		command1
comp2		mode2		command1
		mode2		command2
comp3		mode1		command1
		mode1		command2
i am getting output as 
--------- SHEET:Sheet1
( 0 , 0 ) =>component
( 0 , 2 ) =>mode
( 0 , 4 ) =>command
( 2 , 0 ) =>
( 3 , 0 ) =>comp1
( 3 , 2 ) =>mode1
( 3 , 4 ) =>command1
( 6 , 0 ) =>comp2
( 6 , 2 ) =>mode2
( 6 , 4 ) =>command1
( 7 , 2 ) =>mode2
( 7 , 4 ) =>command2
( 8 , 4 ) =>
( 9 , 0 ) =>comp3
( 9 , 2 ) =>mode1
( 9 , 4 ) =>command1
( 10 , 2 ) =>mode1
( 10 , 4 ) =>command2
but i need to maintain it as hash value ie hash of array of hashes ie 

%hash=(

comp1  => [
            { command => "command1",  mode => "mode1"  },
            { command => "command2",    mode => "mode1"},
          ],  
        
comp2  => [
            { command => "command1",  mode => "mode1"  },
            { command => "command2",    mode => "mode1"},
          ],  
)

[download]
or 
%hash=>{

 comp1=>{

      mode1=>{

              (command => command1)
               (command=>command2)
              }

       mode2=>{

               (command => command1)
               (command=>command2)
              }
          },

  comp2=>{
        mode1=>{

              (command => command1)
               (command=>command2)
              }

       mode2=>{

               (command => command1)
               (command=>command2)
              }
          },
}

[download]
and after building this in to hash ,i will built an text file using key value as comp1_text,comp2_text etc as the filename . My comp1_text will be input to another perl script which will populate my database automatically. 
ie secondscript.pl comp1_text.
my comp1_text will be in format as 
command1:comp1:mode1:
command2:comp1:mode1:
I need help in how to modify my script such that i can bulid my hash in specified format below and write in an text file by creating it in the specified format.
can any one help me in this regard. Thanks, srins.

2006-05-25 Retitled by GrandFather, as per Monastery guidelines
Original title: 'How to build the hash for my above format which i parse from excel file and write that in text file using that hash, delimited by colon values'

READMORE tags added by Arunbear

Replies are listed 'Best First'.
Re: How Do I Populate a Hash of Hashes?
by jdtoronto (Prior) on May 26, 2006 at 09:25 UTC
    Seems like a somewhat convoluted approach, maybe you have decided on a solution without knowing what the problem is.

    However, you should read the Perl Data Structures Cookbook - partr of your documentation for your Perl installation - perldsc.

    Introduce yourself to Data::Dumper and its use as a serialiser.

    jdtoronto

[reply]
Re: how to build the hash for below code
by reasonablekeith (Deacon) on May 26, 2006 at 10:03 UTC
    srins firstly, I think there's far too much information in your post. ++ for trying to give a working example, but it would have been better if you'd cut this down to what you've got problems with, which is building the hash. (The fact that this comes from a spreadsheet is neither here nor there.)

    secondly, I'd be inclined to re-jig your hash format a bit. Your example shows a hash of a hash, containing values which are array references with hashes in them?. Seems a bit overboard, you could get away with just a hash of a hashes containing array refences, but I'd put the array reference in its own named key, just for flexibilty. for example 

    you show
$hash{'mode'}{'component'} = [{'command' => 'cmd1'}, {'command' => 'cm
+d2'}];

simplist would be
$hash{'mode'}{'component'} = ['cmd1', 'cmd2'];

but I'd go for
$hash{'mode'}{'component'}{'commands'} = ['cmd1', 'cmd2'];

    [download]
    ignoring your code and just taking your data, I'd attack this by processing the rows in order, keeping track of the current mode and component, and, when I found a command, sticking it straight into the hash. something like this... 
    #!/perl -w
use Data::Dumper;
use strict;

my %hash;

my $current_comp = "";
my $current_mode = "";

my %the_hash;

while (<DATA>) {
    chomp;
    my ($coord,$data) = split /=>/;
    my ($row,$col) = split /,/, $coord;

    if ($col == 0) {
        $current_comp = $data;
        next;
    } 
    if ($col == 2) {
        $current_mode = $data;
        next;
    } 
    if ($col == 4) {
        push @{$the_hash{$current_mode}{$current_comp}{'commands'}}, $
+data;
        next;
    } 
}
print Dumper(\%the_hash);

    [download]
    the tricksy part of that is the line... 
    push @{$the_hash{$current_mode}{$current_comp}{'commands'}}, $data;

    [download]
    .. which makes perl do all sorts of things for us. Just the fact that we're referencing the hash key "$the_hash{$current_mode}{$current_comp}{'commands'}" makes perl auto-vivify it, then the whole thing is wrapped up as an array refence, Perl again is very obliging, assumes we know what were doing and creates us a hash reference for us which we can then push to.

    The output should end up looking something like this 

    $VAR1 = {
          'mode1' => {
                       'comp3' => {
                                    'commands' => [
                                                    'command1',
                                                    'command2'
                                                  ]
                                  },
                       'comp1' => {
                                    'commands' => [
                                                    'command1'
                                                  ]
                                  }
                     },
          'mode2' => {
                       'comp2' => {
                                    'commands' => [
                                                    'command1',
                                                    'command2',
                                                    ''
                                                  ]
                                  }
                     }
        };

    [download]
    HTH, Rob
    ---
    my name's not Keith, and I'm not reasonable.
[reply]
[d/l]
[select]
Re: How Do I Populate a Hash of Hashes?
by Uncommon (Initiate) on May 25, 2006 at 18:41 UTC

    Hi There,

    Do you actually have to parse the workbook? The reason I ask is that it may be much simpler to save the file as text, and parse the resulting text file.

    A Life Uncommon
[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom