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

Try

#!perl
use strict;
use Spreadsheet::ParseExcel;
use Data::Dump 'dd';

my $filename = "Book2.xls";

my $e = new Spreadsheet::ParseExcel;
my $eBook  = $e->Parse($filename);
my $eSheet = $eBook->{Worksheet}[0];
my $maxrow = $eSheet->{MaxRow};
my $maxcol = $eSheet->{MaxCol};
print "maxrow=$maxrow maxcol=$maxcol\n";

my %data    = ();
my @header  = ();
my @datarow = ();

for my $row (0 .. $maxrow) {

  my %this_set=();
  my $master_key;
  
  for my $col (0 .. $maxcol){
    my $cell = $eSheet->{Cells}[$row][$col];
    if (defined $cell){
      $datarow[$col] = $cell->Value unless ($cell->Value eq '');
    } else {
      next;
    }
  
    # header row
    if ($row == 0){
      $header[$col] = $datarow[$col];
      $datarow[$col] = '';
    }  else {
      if ($col == 0){
        $master_key = $datarow[$col];
      } else {
        my $key   = $header[$col];
        my $value = $datarow[$col];
        $this_set{$key} = $value;
      } 
    }     
  }  
  #print join ",",@datarow,"\n";
 
  # Store our found set for that ID:
  if ($row > 0){
    push @{ $data{ $master_key }}, \%this_set;
  }
}

dd \%data;

[download]
update : clear @datarow for header line.
poj

Replies are listed 'Best First'.
Re^8: Converting Excel to Hash
by ravi179 (Novice) on Jan 04, 2017 at 11:26 UTC 
    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

    .
[reply]
[d/l]
      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]
[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]

In Section Seekers of Perl Wisdom