Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

Re^14: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column

by poj (Abbot)
on Feb 25, 2017 at 13:47 UTC ( [id://1182817]=note: print w/replies, xml ) Need Help??


in reply to Re^13: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column
in thread How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column

A & B & D - see perlretut. Expand the addr regex to include more than just digits

if (/($re).*addr = ([.\da-fA-F:]+)/){

and the other to include all character up to a comma

while (/($re)[ =]*([^,]+)/g){

C & E- Standardize your keywords to a single space private_uid = and process the input lines to collapse multiple spaces down to one

 s/ +/ /g;    # remove multiple spaces

I suspect as a result of expanding the value regex you will capture things you then want to exclude. I have added a basic cleanup sub for you to expand as required. Full changes are

for my $file (@files){ my $line_no = 0; print "Reading $file\n"; open my $fh, '<', $file or die $!; while (<$fh>){ chomp; ++$line_no; s/ +/ /g; # remove multiple spaces # special pcscf_v4 extract addr if (/($re).*addr = ([.\da-fA-F:]+)/){ my $msg = update_sheet($1,$2,$_); print "Line $line_no $msg special\n"; next; } # match more than 1 per line e.g. usim and isim while (/($re)[ =]*([^,]+)/g){ my $msg = update_sheet($1,$2,$_); print "Line $line_no $msg\n"; } } close $fh; } # update sheet sub update_sheet { my ($kw,$value,$comment) = @_; my $clean = cleanup($kw,$value); my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2]; my $sel = $Book->Worksheets($sht)->Cells($r,$c+1); $sel->Insert({ Shift => -4161}); # xlToRight $sel->{Value} = $clean; $sel->AddComment( { Text=> $comment } ); $sel->Comment->Shape->TextFrame->{ AutoSize } = 1; $kwhashref->{$kw}[3] = $value; # for debugging $kwhashref->{$kw}[4] = $clean; # for debugging return "kw=[$kw] value=[$value] clean=[$clean]"; } # remove unwanted chars from value sub cleanup { my ($kw,$value) = @_; # generic cleansing $value =~ s/[\[\]]//g; # remove all [] brackets $value =~ s/^\s+//g; # remove leading whitespace $value =~ s/\s+$//g; # remove trailing whitespace # specific cleansing #if ($kw =~ //){ # $value =~ s///g #} return $value; }
poj

Replies are listed 'Best First'.
Re^15: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column
by rockyurock (Acolyte) on Feb 25, 2017 at 16:41 UTC
    Oops! I am getting an Infinite loop running using the above changes . Kindly help! below Is the latest overall code.
    #!perl use strict; use warnings; use Data::Dump 'pp'; use Win32::OLE; $Win32::OLE::Warn = 3; # get already active Excel application or open new my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); # open Excel file my $dir = "E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\\ +Excel\\"; my $excelfile = 'UE_NW_Parameters.xlsx'; my $Book = $Excel->Workbooks->Open($dir.$excelfile) or die Win32::OLE->LastError(); # get keywords my $kwhashref = get_keywords($Book); # create regex my $match = join '|',map{quotemeta} keys %$kwhashref; my $re = qr/$match/; #print $re; # search files and update workbook my @files = grep { -f } (<main_log*>); for my $file (@files){ my $line_no = 0; print "Reading $file\n"; open my $fh, '<', $file or die $!; while (<$fh>){ chomp; ++$line_no; s/ +/ /g; # remove multiple spaces # special pcscf_v4 extract addr if (/($re).*addr = ([.\da-fA-F:]+)/){ my $msg = update_sheet($1,$2,$_); print "Line $line_no $msg special\n"; next; } # match more than 1 per line e.g. usim and isim while (/($re)[ =]*([^,]+)/g){ my $msg = update_sheet($1,$2,$_); print "Line $line_no $msg\n"; } } close $fh; } #pp $kwhashref; # debug # save as new spreadsheet $Book->SaveAs($dir.'updated_'.$excelfile); $Book->Close; # update sheet sub update_sheet { my ($kw,$value,$comment) = @_; my $clean = cleanup($kw,$value); my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2]; my $sel = $Book->Worksheets($sht)->Cells($r,$c+1); $sel->Insert({ Shift => -4161}); # xlToRight $sel->{Value} = $clean; $sel->AddComment( { Text=> $comment } ); $sel->Comment->Shape->TextFrame->{ AutoSize } = 1; $kwhashref->{$kw}[3] = $value; # for debugging $kwhashref->{$kw}[4] = $clean; # for debugging return "kw=[$kw] value=[$value] clean=[$clean]"; } # remove unwanted chars from value sub cleanup { my ($kw,$value) = @_; # generic cleansing $value =~ s/[\[\]]//g; # remove all [] brackets $value =~ s/^\s+//g; # remove leading whitespace $value =~ s/\s+$//g; # remove trailing whitespace # specific cleansing #if ($kw =~ //){ # $value =~ s///g #} return $value; } # get keywords sub get_keywords { my ($book) = @_;; my %word = (); for my $sht (1..$book->Worksheets->Count){ my $Sheet = $book->Worksheets($sht); # determine position of keywords # $Sheet->UsedRange->Value; my $rng = $Sheet->UsedRange->Address( { ReferenceStyle => -4150 } ); # R1C1:R2C2 my ($r1,$c1,$r2,$c2) = ($rng =~ m/[RC](\d+)/g); $r2 = $r2 || $r1; $c2 = $c2 || $c1; print "Sheet $sht Range $rng r1=$r1 c1=$c1 r2=$r2 c2=$c2\n"; # build keyword hash storing cell position for my $c ($c1..$c2){ for my $r ($r1..$r2){ my $val = $Sheet->Cells($r,$c)->Value || ''; if ($val){ $word{$val} = [$sht,$r,$c]; # sheet row col } } } } return \%word; }
      sorry , pls Ignore my last post of Infinite loop as It was taking much + time because of parsing each and every line.. Now with latest code , I am started getting below Issues with exisitin +g working data kewords isim = variable also started printing text after the value i.e. 4 text + (vendor/HELP) also getting printed in the cel which I don;t want. Same Is happening with all other variables as well like -> usim/sim_in +valid ...etc. Kindly help!

        As I said, you probably need to add cleaning to specific keywords in the cleanup sub

        # specific cleaning for isim usim sim_invalid if ( $kw =~ /^(isim|usim|sim_invalid)/ ){ $value =~ s/(\d+).*/$1/; # remove trailing text }
        poj

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2024-03-28 14:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found