CODEWITHSUNDEEP
excelperl
paaven
paaven

Reputation: 15

Perl - How to set key based on column header when converting from xlsx to perl hash

I have a xlsx that im converting into a perl hash

Name Type Symbol Colour
JOHN SUV X R
ROB MPV Y B
JAMES 4X4 Y G

Currently, I can only set the hash superkey to the column wanted based on column array. I cant seem to figure out how to choose based on column header.

use Data::Dumper;


use Text::Iconv;
my $converter = Text::Iconv->new("utf-8", "windows-1251");



use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX->new('file.xlsx', $converter);

foreach my $sheet (@{$excel->{Worksheet}}) {
if ($sheet->{Name} eq "sheet1"){

my %data;

for my $row ( 0 .. $sheet-> {MaxRow}) {
if  ($sheet->{Cells}[0][$col]->{Val} eq "Symbol"){
    my $super_key = $sheet->{Cells}[$row][$col]{Val};

}
    my $key       = $sheet->{Cells}[$row][0]{Val};
    my $value = $sheet->{Cells}[$row][2]{Val};
    my $value2= $sheet->{Cells}[$row][3]{Val};
    $data{$super_key}->{$key}->{$value}=${value2};
}

print Dumper \%data;

}}

The outcome i get is,

$VAR1 = {
          '' => {
                  'JOHN' => {
                             'SUV' => R

I would like to have;

$VAR1 = {
          'X' => {
                  'JOHN' => {
                             'SUV' => R

`

Upvotes: 1

Views: 92

Answers (1)

Georg Mavridis
Georg Mavridis

Reputation: 2331

You are missing use strict; in your perl script. If you had it, you would have seen your error yourself

Defining the $super_key with my in your If-clause, makes this variable lose scope as soon as you exit it.

And using a variable $col without defining it doesn't work either.

Better (and probably working) is:

for my $row ( 0 .. $sheet-> {MaxRow}) {
    my $super_key;
    foreach my $col (0 .. 3) {
        if  ($sheet->{Cells}[0][$col]->{Val} eq "Symbol"){
            $super_key = $sheet->{Cells}[$row][$col]{Val};
        }
    }
    my $key       = $sheet->{Cells}[$row][0]{Val};
    my $value = $sheet->{Cells}[$row][2]{Val};
    my $value2= $sheet->{Cells}[$row][3]{Val};
    $data{$super_key}->{$key}->{$value}=${value2};
}

Upvotes: 2

Related Questions