http://qs1969.pair.com?node_id=808195

blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Bestest Monks

I am trying to creat a data structure like;
San device : SAN3 Lun Name : LUNName3 WW Names : 60:05:08:b2:00:bb:b6:93 60:05:08:b2:00:bb:b6:92 San device : SAN4 Lun Name : LUNName4 WW Names : 66:05:08:b2:00:7d:15:13 66:05:08:b2:00:7d:15:12 66:01:43:80:00:be:79:12 66:01:43:80:00:be:79:10
And so forth...

I wrote this bit of code which is not working!
my $xls_path = "c:/path_to_xls.xlsx"; my $xls = $excel->Workbooks->Open("$xls_path"); foreach my $sheet ( in $xls->Worksheets ) { $San ={}; $San->{name} = $sheet->Name, $/; my $San_data = $xls->Worksheets("$San->{name}"); my @Colum_A = $San_data->UsedRange->{'Value'}; if ( "$Colum_A[0]" =~ /ARRAY/) { $San->{note} = 'Live SAN Box'; foreach my $array (@{$Colum_A[0]}) { foreach (@$array) { my @wwn; next if ((uc $_) eq (uc "$San->{name}")); next if not defined $_; push (@{$San->{LUNs}},$_) if ($_ !~ /:/); foreach my $LUN (@{$San->{LUNs}}) { print "\n".$LUN.": ".$_."\n" if ($_ =~ /:/); } } } } else { $San->{note} = $Colum_A[0]; } #print Dumper $San; print "\n__________\n"; } __DATA__ LUNName1 60:05:08:b2:00:bb:b6:93 60:05:08:b2:00:bb:b6:92 LUNName1 66:05:08:b2:00:7d:15:13 66:05:08:b2:00:7d:15:12 66:01:43:80:00:be:79:12 66:01:43:80:00:be:79:10 LUNName3 66:05:08:b2:00:b2:95:23 66:05:08:b2:00:b2:95:22 66:05:08:b2:00:b2:ab:a3 89:05:08:b2:00:b2:ab:a2 89:05:08:b2:00:b2:b4:c3 89:05:08:b2:00:b2:b4:c2 LUNName4 89:05:08:b2:00:b2:36:c2 89:05:08:b2:00:b9:fa:13 89:05:08:b2:00:7c:bc:03 98:05:08:b2:00:b9:fa:12 70:05:08:b2:00:7c:bc:02 70:05:08:b2:00:b2:2c:b3 70:05:08:b2:00:b2:2c:b2 70:05:08:b2:00:75:6d:73
can someone pretty please advise how can I creat such data structures from the __DATA__ that I have?

In Fact; I don't slurp the data in, I populate an array direct from excel with
my @Colum_A = $San_data->UsedRange->{'Value'};
so I think this would be better
@Colum_A = qw/LUNName1 60:05:08:b2:00:bb:b6:93 60:05:08:b2:00:bb:b6:92 LUNName1 66:05:08:b2:00:7d:15:13 66:05:08:b2:00:7d:15:12 66:01:43:80:00:be:79:12 66:01:43:80:00:be:79:10 LUNName3 66:05:08:b2:00:b2:95:23 66:05:08:b2:00:b2:95:22 66:05:08:b2:00:b2:ab:a3 89:05:08:b2:00:b2:ab:a2 89:05:08:b2:00:b2:b4:c3 89:05:08:b2:00:b2:b4:c2 LUNName4 89:05:08:b2:00:b2:36:c2 89:05:08:b2:00:b9:fa:13 89:05:08:b2:00:7c:bc:03 98:05:08:b2:00:b9:fa:12 70:05:08:b2:00:7c:bc:02 70:05:08:b2:00:b2:2c:b3 70:05:08:b2:00:b2:2c:b2 70:05:08:b2:00:75:6d:73/;
Thanks

Blackadder

Replies are listed 'Best First'.
Re: Creating a table from raw data!
by BioLion (Curate) on Nov 19, 2009 at 16:21 UTC

    You haven't told us what __DATA__ you have or what data structure you want out at the end of it!

    If you put the input as tab delimited text so people can repeat the read in of data and give us an actual data structure you want to get to ( either in words i.e. hash of hashes, or as example code like the output of Data::Dumper ), we may be able to help you better!

    Just a something something...
      Thanks BioLion

      You are absolutely correct. I've updated my post with __DATA__

      Blackadder

        OK, but I am still struggling a little bit - what is the actual contents of @Colum_A?:

        use Data::Dumper qw/Dumper/; my $xls_path = "c:/path_to_xls.xlsx"; my $xls = $excel->Workbooks->Open("$xls_path"); foreach my $sheet ( in $xls->Worksheets ) { $San ={}; $San->{name} = $sheet->Name, $/; my $San_data = $xls->Worksheets("$San->{name}"); my @Colum_A = $San_data->UsedRange->{'Value'}; ## what datastructure are we working with? print "SAN : $San->{name}\n"; print Dumper \@Colum_A; }

        If it is a long array like you say then why not take a simple approach?:

        use strict; use warnings; use Data::Dumper qw/Dumper/; #my $xls_path = "c:/path_to_xls.xlsx"; #my $xls = $excel->Workbooks->Open("$xls_path"); ## just for testing my $xls = [qw/san1 san2/]; my $data; #foreach my $sheet ( $xls->Worksheets ){ for my $sheet ( @$xls ){ print "Data in SAN : $sheet\n"; ## store each san as a hashref my $San ={}; #$San->{name} = $sheet->Name, $/; $San->{name} = $sheet; #my $San_data = $xls->Worksheets("$San->{name}"); #my @Colum_A = $San_data->UsedRange->{'Value'}; ## again, just for testing my @Colum_A = qw/ RDUB010 34:34:36:7U 87:23:89:6C RDUB090 90:39:12:6U 34:34:36:7Z 87:23:89:6N 34:38:36:7U /; ## process each array element and print something appropriate ## also store data into a HoH / HoA my $current = 'not_defined'; # place holder foreach my $element (@Colum_A){ if ($element =~ /^RD/){ print "\tSAN : $San->{name}\n\t - LUN : $element\n"; $current = $element; } elsif ( $element =~ /^(?:[^:]{2}:){3}[^:]{2}$/){ # untested! should match # 60:05:08:b2:00:bb:b6:93 print "\t\tWW : $element\n"; push @{ $San->{LUN}->{$current} }, $element; } else { warn "Unrecognised input : \'$element\'\n"; } } ## add San to data push @$data, $San; } ## print out data structure print "\n-----> DATA\n"; print Dumper $data; ## and again print "\n-----> OUTPUT\n"; foreach my $san (@$data){ print "SAN : $san->{name}\n"; foreach my $lun( keys %{ $san->{LUN} } ){ print " - LUN : $lun\n"; print "\tWW : $_\n" for @{ $san->{LUN}->{$lun} } } }

        This produces what you seem to be looking for:

        Update bugfixed code... Note to self - DO NOT POST UNTESTED REGEXES...

        Just a something something...