Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: Creating a table from raw data!

by BioLion (Curate)
on Nov 19, 2009 at 16:21 UTC ( [id://808198]=note: print w/replies, xml ) Need Help??


in reply to Creating a table from raw data!

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...

Replies are listed 'Best First'.
Re^2: Creating a table from raw data!
by blackadder (Hermit) on Nov 19, 2009 at 16:29 UTC
    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...
        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
        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
        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; }
        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' };
        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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://808198]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (5)
As of 2024-04-13 21:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found