469,361 Members | 2,391 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,361 developers. It's quick & easy.

creating a hash of hash from excel file

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
6 5616
KevinADC
4,059 Expert 2GB
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
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
3,503 Expert Mod 2GB
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
4,059 Expert 2GB
@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
bazz
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
3,503 Expert Mod 2GB
@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.

Similar topics

6 posts views Thread by Jeremy Langworthy | last post: by
4 posts views Thread by Del | last post: by
3 posts views Thread by A.M | last post: by
2 posts views Thread by morph276 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.