By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,773 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,773 IT Pros & Developers. It's quick & easy.

creating a hash of hash from excel file

P: 41
hi,

i have one excel file, i want to create a hash of hash from the excel file, like:

Expand|Select|Wrap|Line Numbers
  1. %hash=
  2. [
  3.  sheet1 => [
  4.               {
  5.                 'VALUES' => 'tryrt',
  6.                 'ROWNUM' => 13,
  7.                 'COLUMNNUM' => 1
  8.               },
  9.               {
  10.                 'VALUES' => 'Hyderabad',
  11.                 'ROWNUM' => 7,
  12.                 'COLUMNNUM' => 1
  13.               },
  14.             ],
  15.  sheet2 => [
  16.       {
  17.               'VALUES' => 'tryrsdft',
  18.               'ROWNUM' => 1sdf3,
  19.               'COLUMNNUM' => 1sdf
  20.              },
  21.              {
  22.               'VALUES' => 'Hyddserabad',
  23.               'ROWNUM' => 7ds,
  24.               'COLUMNNUM' => 1342
  25.              },
  26.     ],
  27. ....
  28. ....
  29. ];


Expand|Select|Wrap|Line Numbers
  1. use Spreadsheet::ParseExcel;
  2. use Data::Dumper;
  3. my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('source.xls');
  4. my($Row, $Col, $Wrksht, $oWkC);
  5. foreach my $Wrksht(@{$oBook->{Worksheet}}) 
  6. {
  7.  my $sheetname = $Wrksht->{Name};
  8.     for(my $Row = $Wrksht->{MinRow}; defined $Wrksht->{MaxRow} && $Row <= $Wrksht->{MaxRow} ; $Row++) 
  9.  {
  10.   for(my $Col = $Wrksht->{MinCol}; defined $Wrksht->{MaxCol} && $Col <= $Wrksht->{MaxCol} ; $Col++) 
  11.   {
  12.    $oWkC = $Wrksht->{Cells}[$Row][$Col];
  13.             if ($oWkC) 
  14.    {
  15.  
  16.     my %HoAexcel = ( SHEETNAME => $sheetname, COLUMNNUM => $Col+1, ROWNUM => $Row+1, VALUES => $oWkC->Value, );
  17.  
  18.     push (@loop, \%HoAexcel);
  19.  
  20.    }
  21.         }
  22.     }
  23. }
  24. print Dumper(@loop);
I have written a script
But i am not able to create like this.
after running that script, the result is diplaying like this:
Expand|Select|Wrap|Line Numbers
  1. $VAR1 = {
  2.           'VALUES' => '1',
  3.           'ROWNUM' => 1,
  4.           'COLUMNNUM' => 1
  5.         };
  6. $VAR2 = {
  7.           'VALUES' => '2',
  8.           'ROWNUM' => 2,
  9.           'COLUMNNUM' => 1
  10.         };
  11. $VAR3 = {
  12.           'VALUES' => '1231',
  13.           'ROWNUM' => 3,
  14.           'COLUMNNUM' => 1
  15.         };
  16.  
Can somebody help me to creating above multi dimensional hash.
Jan 23 '09 #1
Share this Question
Share on Google+
6 Replies


KevinADC
Expert 2.5K+
P: 4,059
Your first example data is not a hash of hashes. It is an array of arrays of hashes. In the perl code you posted, change this line:

print Dumper(@loop);

change it to:

print Dumper \@loop;

and see what the output looks like.
Jan 23 '09 #2

P: 41
Hi KevinADC,

After changing the line from print Dumper(@loop) to print Dumper \@loop;

the o/p is like:
Expand|Select|Wrap|Line Numbers
  1. $VAR1 = [
  2.          { 
  3.            'VALUES' => '1', 
  4.            'ROWNUM' => 1, 
  5.            'COLUMNNUM' => 1 
  6.          },
  7.          { 
  8.            'VALUES' => '2', 
  9.            'ROWNUM' => 2, 
  10.            'COLUMNNUM' => 1 
  11.          }, 
  12.          { 
  13.            'VALUES' => '1231', 
  14.            'ROWNUM' => 3, 
  15.            'COLUMNNUM' => 1 
  16.          }
  17.  ]; 
  18.  
But I need the the excel sheet name in this hash of hash.

Tx,
Dillip.
Jan 27 '09 #3

numberwhun
Expert Mod 2.5K+
P: 3,503
dillipkumar,

We have corrected your code tags a couple of times now. Please be sure and use them the next time you post code into the forums.

Regards,

Jeff
Jan 27 '09 #4

KevinADC
Expert 2.5K+
P: 4,059
@dillipkumar
My only suggestion is to read the module documentation. Apparently this line returns a reference to an array of hashes:

Expand|Select|Wrap|Line Numbers
  1. my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('source.xls');
  2.  
Look over the module and see if it has a method for returning a hash of hashes if you're sure thats what you really want. The array is probably used to keep the original order of the records. If you use a hash of hashes the original order of the records will most likely not be retained.
Jan 27 '09 #5

P: 3
what do you get if you dump the \%HoAexcel at the same point in your script as the print Dumper(@loop);

bazz
Oct 5 '09 #6

numberwhun
Expert Mod 2.5K+
P: 3,503
@bazz
I hate to say this, but this thread is about 8 months old. I am sure the OP either solved this or moved on. Please keep an eye on the age of the thread you are viewing.

Also, you may want to try PMing the OP who asked the question instead of looking like you are hijacking their thread.

Regards,

Jeff
Oct 5 '09 #7

This discussion thread is closed

Replies have been disabled for this discussion.