Re^3: Creating a table from raw data!

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;
}
[download]

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} }
    }
}
[download]

This produces what you seem to be looking for:

Data in SAN : san1
    SAN : san1
     - LUN : RDUB010
        WW : 34:34:36:7U
        WW : 87:23:89:6C
    SAN : san1
     - LUN : RDUB090
        WW : 90:39:12:6U
        WW : 34:34:36:7Z
        WW : 87:23:89:6N
        WW : 34:38:36:7U
Data in SAN : san2
    SAN : san2
     - LUN : RDUB010
        WW : 34:34:36:7U
        WW : 87:23:89:6C
    SAN : san2
     - LUN : RDUB090
        WW : 90:39:12:6U
        WW : 34:34:36:7Z
        WW : 87:23:89:6N
        WW : 34:38:36:7U

-----> DATA
$VAR1 = [
          {
            'LUN' => {
                       'RDUB090' => [
                                      '90:39:12:6U',
                                      '34:34:36:7Z',
                                      '87:23:89:6N',
                                      '34:38:36:7U'
                                    ],
                       'RDUB010' => [
                                      '34:34:36:7U',
                                      '87:23:89:6C'
                                    ]
                     },
            'name' => 'san1'
          },
          {
            'LUN' => {
                       'RDUB090' => [
                                      '90:39:12:6U',
                                      '34:34:36:7Z',
                                      '87:23:89:6N',
                                      '34:38:36:7U'
                                    ],
                       'RDUB010' => [
                                      '34:34:36:7U',
                                      '87:23:89:6C'
                                    ]
                     },
            'name' => 'san2'
          }
        ];

-----> OUTPUT
SAN : san1
 - LUN : RDUB090
    WW : 90:39:12:6U
    WW : 34:34:36:7Z
    WW : 87:23:89:6N
    WW : 34:38:36:7U
 - LUN : RDUB010
    WW : 34:34:36:7U
    WW : 87:23:89:6C
SAN : san2
 - LUN : RDUB090
    WW : 90:39:12:6U
    WW : 34:34:36:7Z
    WW : 87:23:89:6N
    WW : 34:38:36:7U
 - LUN : RDUB010
    WW : 34:34:36:7U
    WW : 87:23:89:6C
[download]

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

Just a something something...

Comment on Re^3: Creating a table from raw data! Select or Download Code

Replies are listed 'Best First'.
Re^4: Creating a table from raw data! by blackadder (Hermit) on Nov 19, 2009 at 19:09 UTC
Spent ages debugging the above to no avail,..still didn't work! I started again Now; I have this data in colum A in excel `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` [download] And i want to create a data structure like this `San : SAN_01 Lun: RDUB010 WWn1: 34:34:36:7U WWn2: 34:34:36:7U Lun: RDUB090 WWn1:90:39:12:6U WWn2:34:34:36:7Z WWn3:87:23:89:6N WWn4:34:38:36:7U San: SAN_02 . . . and so forth` [download] So I put this PERL script together to do the job; #! c:/perl/bin/perl.exe # # use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Data::Dumper qw/Dumper/; use vars qw/$Storage/; my $data; $Win32::OLE::Warn =2; my $excel = Win32::OLE->GetActiveObject('Excel.Application') \|\| Win32: +:OLE->new('Excel.Application'); my $xls_path = "c:\\test.xlsx"; my $xls = $excel->Workbooks->Open("$xls_path"); foreach my $sheet ( in $xls->Worksheets ) { $Storage ={}; $Storage->{SAN} = $sheet->Name, $/; print $Storage->{SAN}."\n"; my $San_data = $xls->Worksheets("$Storage->{SAN}"); my @Colum_A = $San_data->UsedRange->{'Value'}; foreach my $array (@Colum_A) { foreach my $element (@$array) { foreach my $info (@$element) { if ($info !~ /:/) { next if not defined $info; push (@{$Storage->{LUNs}}, "$info"); } if ($info =~ /:/) { push (@{$Storage->{LUNs}}->{WWN}}, "$info"); } } } print "\n_______________\n"; } print Dumper $Storage; } [download] When I ran the script I get this output, which is wrong `SAN_01 ______________ $VAR1 = { 'ARRAY(0x1b68610)' => { 'WWN' => [ '34:34:36:7U', '87:23:89:6C', '90:39:12:6U', '34:34:36:7Z', '87:23:89:6N', '34:38:36:7U', ] }, 'LUNs' => [ 'RDUB010', 'RDUB090' ], 'SAN' => 'SAN_01' };` [download] Could someone for the love of God or Perl tell me where am I going wrong and what can I do to get it right? THANKS A BUNCH Blackadder	[reply] [d/l] [select]
Re^5: Creating a table from raw data! by graff (Chancellor) on Nov 19, 2009 at 22:24 UTC
I'm sorry, but the code you just posted could not have produced the output that you posted with it. The script has a syntax error on this line (inside the last "if" block, line 42 when I downloaded it): `push (@{$Storage->{LUNs}}->{WWN}}, "$info");` [download] Note the extra close-curly-brace. I think if you change it to read as follows: `push (@{$$Storage{LUNs}{WWN}}, $info);` [download] there won't be a syntax error, and it might behave better. Apart from that, there's a warning from this line: `$Storage->{SAN} = $sheet->Name, $/;` [download] The "`, $/`" part does no good at all -- the warning is "useless use of a variable in void context". Just remove those four characters. There's nothing more I can say, because I don't use windows or Win32::OLE. To read from an Excel file, I use Spreadsheet::ParseExcel, which works fine.	[reply] [d/l] [select]
Re^5: Creating a table from raw data! by johngg (Canon) on Nov 19, 2009 at 23:32 UTC
I'm not sure if this the sort of data structure you are after. It isn't the same as your structure but I think it reflects the hierarchy implied by your data. I have hopefully managed to replicate the sort of data you have in the spreadsheet in my @sanData array. use strict; use warnings; use Data::Dumper; # @sanData is equivalent to $sheet->Name() and @Column_A data # in your 'foreach my $sheet' loop. my @sanData = ( { name => q{SAN_01}, data => [ 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 } ], }, { name => q{SAN_02}, data => [ qw{ RDUB030 34:7U:34:36 87:89:23:6C RDUB070 90:12:39:7Z 36:34:34:6U 89:23:87:6N 34:36:38:7U } ], }, ); my $storage = {}; foreach my $rhSheet ( @sanData ) { my $name = $rhSheet->{ name }; $storage->{ $name } = {}; my $currentLUN = q{}; foreach my $dataItem ( @{ $rhSheet->{ data } } ) { if ( $dataItem =~ m{:} ) { push @{ $storage->{ $name }->{ $currentLUN } }, $dataItem; } else { $currentLUN = $dataItem; $storage->{ $name }->{ $currentLUN } = []; } } } print Data::Dumper->Dumpxs( [ $storage ], [ qw{ storage } ] ); [download] The Data::Dumper output. `$storage = { 'SAN_01' => { 'RDUB090' => [ '90:39:12:6U', '34:34:36:7Z', '87:23:89:6N', '34:38:36:7U' ], 'RDUB010' => [ '34:34:36:7U', '87:23:89:6C' ] }, 'SAN_02' => { 'RDUB070' => [ '90:12:39:7Z', '36:34:34:6U', '89:23:87:6N', '34:36:38:7U' ], 'RDUB030' => [ '34:7U:34:36', '87:89:23:6C' ] } };` [download] I hope this is helpful. Cheers, JohnGG	[reply] [d/l] [select]
Re^5: Creating a table from raw data! by BioLion (Curate) on Nov 20, 2009 at 10:18 UTC
I bugfixed my code... I shouldn't post untested stuff! It seems to give what you are after. graff and johngg have also given you good approaches, so hopefully you are all set now. It seems like getting the data wasn't the problem, but creating and navigating complex datastructures - whenever I am having problems like this, i find perldsc and perlref very helpful for avoiding frustrations! And as far as deciding on the most appropriate datastructure, that is really down to experimenting and seeing what works for you! Just a something something...	[reply]