in reply to Re^7: Converting Excel to Hash
in thread Converting Excel to Hash

use Spreadsheet::ParseExcel;
use Data::Dumper;
$filename="Book2.xls";
$e=new Spreadsheet::ParseExcel;
$eBook=$e->Parse($filename);


$sheets = $eBook->{SheetCount};
($eSheet, $sheetName);

foreach $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    print "Worksheet $sheet: $sheetName\n";

 %set =();
%data =();


foreach $row( 1 .. $eSheet->{MaxRow} )

{

if (defined  ($eSheet->{Cells}[$row][0] ))
{
$master_key=($eSheet->{Cells}[$row][0]->Value);
$r=$row;
}
else
{
$master_key=$master_key;
}
foreach $col(1 .. $eSheet->{MaxCol})

{
my $key=$eSheet->{Cells}[0][$col]->Value;

if (defined $eSheet->{Cells}[$row][$col])
{

 $val=($eSheet->{Cells}[$row][$col]->Value);

}
else 
{

$val=$eSheet->{Cells}[$r][$col]->Value;

}

$set{$key}=$val;

}

if ($row>0)
{
}
push @{ $data{$master_key}}, \%set;

}
print Dumper \%data;

}C:\perlpractice>c.pl              

 Worksheet 0: Sheet1                   
  $VAR1 = {       '1' => [                                         {  
+                         'clz' => 'iir',                             
+     
'                         degree' => 'b.tch          
                           'name' => 'ravi' 
                     },                                        .      
+          $VAR1->{'1'}[0]        
               ],                              
   '2' => [    $VAR1->{'1'}[0]   ]                              };

[download]

Iam getting output in the above fashion.Please help .I want to store the data in a Hash

.

Replies are listed 'Best First'.
Re^9: Converting Excel to Hash
by hippo (Archbishop) on Jan 04, 2017 at 11:34 UTC
    I want to store the data in a Hash

    The data is in a hash: %data. You can tell it's a hash because of the % symbol and also because when dumped the keys are separated from the values by the => symbol.

    If you want the data structured differently you will have to specify precisely what that different structure should be. Again, perhaps a careful read through perldsc will help.

[reply]
[d/l]
[select]
      Excel Format:

ID  Name  Degree  CLZ
1   Teja   ph.d   nit
           ph.d   iit
2   Ravi   B.tech  iir

desired format:

$var1 ='1';
$var2=[
       { clz=>nit,
         degree=>ph.d,
         name=>teja
}
]
$var1='1'
$var2=[
     { clz=>iit,
       degree=>ph.d,
       name=>teja
     }
]
$var1='2'
$var2=[
     { clz=>iir,
       degree=>b.tech,
       name=>ravi.
     }
]

      [download]
[reply]
[d/l]

        When I posted my code, I added some debug output for when lines from the input are skipped by your code. Maybe you want to look at that debugging output to find when your code skips input.

        Currently your code does not handle the case of the id being missing in the input. Maybe you want to change that.

[reply]
Re^9: Converting Excel to Hash
by poj (Abbot) on Jan 04, 2017 at 11:59 UTC
[reply]
[d/l]
[select]
      $VAR1 = {
       '1' => [
                {
                  'clz' => 'iir',
                  'degree' => 'b.tch',
                  'name' => 'ravi'
                },
                {
                  'clz' => 'iir',
                  'degree' => 'b.tch',
                  'name' => 'ravi'
                }
              ],
       '2' => [
                {
                  'clz' => 'iir',
                  'degree' => 'b.tch',
                  'name' => 'ravi'
                }
              ]
     };

      [download]

      This is the output iam getting for print Dumper %data

      \ 
      1$VAR1 = {
          'clz' => 'nit',
          'degree' => 'ph.d',
          'name' => 'teja'
        };
1$VAR1 = {
          'clz' => 'iit',
          'degree' => 'm.tech',
          'name' => 'teja'
        };
2$VAR1 = {
          'clz' => 'iir',
          'degree' => 'b.tch',
          'name' => 'ravi'
        };

      [download]

      this is the output iam getting for Print Dumper %set

[reply]
[d/l]
[select]

        You need to create a new %set hash for each row

        ##my %set  =(); <- move this line inside loop
my %data =();
my ($r,$val,$master_key);
 
for my $row (1 .. $eSheet->{MaxRow}){
  my %set  =(); # <- move to here
  ..

        [download]
        poj
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom