in reply to Re^12: 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


thank you ! I have got the data I wanted for these keywords but when t
+rying to get the more generic output ,I got the below tiny errors whe
+n I Issued large Input data

A.

As there was special handling of addr field In keyword pcscf_v6 to pic
+k up the value 

36.5.2.0.3.48.21.129.0.0.0.0.0.0.0.27
but when my Input data has below value for src IP = & dst = It;s picki
+ng up only fields 2405
src = 2405:200:330:1581::1b:5067
dst = 2405:204:198f:c88d::2014:e8a1:50048



B. I am not able to get the value of variable ip_type = (Shoule pick u
+p automatically)
ip_type = IPV4V6
integrity_algo = hmac-md5
encry_algo = aes-cbc

C. I am not able to get the value of variable private_uid    = (may be
+ bacause of too many spaces between Variable 

& = sign)

private_uid    =

D. the below vairable Is picking up Incorrect value (i.e. 0) (may be b
+ecause of : sign rather than = sign)

[CM]struct_no:0x57
[IM]struct_verno:0x58

E. Not able to get values of these variables (may be because of under 
+[] sign)

profile.associated_uri =
profile.home_uri       =
profile.imei           =
profile.private_uid    =
profile.public_uid     =
evs_mode_switch = 
EVS payload type= 

Not sure , but can we somehow generalize to pick the value Irrespecitv
+e of length say until we reach , sign starting next variable.

[download]
My Latest Input data 
8077  8116 D type hexa usim = 1, isim = 1
8077  8116 D type hexa usim = 1, isim = 1
pcscf_v4 : num = 2, protocol_type = 0x21, port_num = 0, addr = 10.56.5
+.85
8088 1223 D temp sim_invalid = 0
8099 1223 XX is_ems_support =   1 #88
8099 1224 XX pdn_act_ind->pcscf_discovery_flag  =  1
ind->signaling_flag            =  1
some text here plmn = 405872
DefaultStatusBarPlmnPlugin: into updateCarrierLabel
ipv6 = 36.5.2.4.25.143.200.141.0.0.0.0.32.20.232.161
get_ipv6_prefix,temp
ipv4 = 10.167.185.101
_send_ipv4_notify
info.is_ems_support =   1
act_ind->emergency_indication =  1
access_rat_type       =   0
transaction_id = 2, rat_type = 0
signaling_flag = 1, ran_type = 0
3750  3771 I read from , ip_type = IPV4V6,
src = 2405:204:198f:c88d::2014:e8a1:50048, dst = 2405:200:330:1581::1b
+:5067
profile.private_uid    =[001001000010459@ims.mnc001.mcc001.3gppnetwork
+.org]
[CM]struct_no:0x57
[IM]struct_verno:0x58
integrity_algo = hmac-md5
encry_algo = aes-cbc
profile.associated_uri =[sip:+917011021677@ims.mnc872.mcc405.3gppnetwo
+rk.org,tel:+917011021677]
profile.home_uri       =[ims.mnc872.mcc405.3gppnetwork.org] 
profile.imei           =[351901039128255] 
profile.private_uid    =[405872000010459@ims.mnc872.mcc405.3gppnetwork
+.org]
profile.public_uid     =[sip:+917011021677@ims.mnc872.mcc405.3gppnetwo
+rk.org] 
evs_mode_switch = (0)
EVS payload type=(127)

[download]

Latest Program
#!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;
    
    # Original
    # # special pcscf_v4 extract addr
    # if (/($re).*addr = ([.\d]+)/){
      # print "Line $line_no kw=[$1] value=[$2] special\n"; 
      # update_sheet($1,$2);
      # next;
    # }
    
    # # match more than 1 per line e.g. usim and isim   
    # while (/($re)[ =]*([.\d]+)/g){
      # print "Line $line_no kw=[$1] value=[$2]\n"; 
      # update_sheet($1,$2);
    # }
    
    if (/($re).*addr = ([.\d]+)/){
    print "LIne $line_no kw=[$1] value=[$2] special\n"; 
    update_sheet($1,$2,$_);
    next;
    }
    
    # match more than 1 per line e.g. usim and isim   
    while (/($re)[ =]*([.\d]+)/g){
      print "Line $line_no kw=[$1] value=[$2]\n"; 
      update_sheet($1,$2,$_);
    }
    
  }  
  close $fh;
}
#pp $kwhashref; # debug

# save as new spreadsheet
$Book->SaveAs($dir.'updated_'.$excelfile);         
$Book->Close;

# update sheet
# original
# sub update_sheet {
  # my ($kw,$value) = @_;
  # my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2];
  # $Book->Worksheets($sht)->Cells($r,$c+1)->{Value} = $value;
  # $kwhashref->{$kw}[3] = $value; # for debugging
# }

  sub update_sheet {
  my ($kw,$value,$comment) = @_;
  my ($sht,$r,$c) = @{$kwhashref->{$kw}}[0..2];
  my $sel = $Book->Worksheets($sht)->Cells($r,$c+1);
  $sel->Insert({ Shift => -4161}); # xlToRight
  $sel->{Value} = $value;
  $sel->AddComment( { Text=> $comment } );
  $kwhashref->{$kw}[3] = $value; # for debugging
    }


# 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;
}

[download]

My Latest Keywords

isim =
usim =
sim_invalid =
pcscf_discovery_flag
signaling_flag
ran_type
emergency_ind
rat
pcscf_v4
pcscf_v6
plmn
is_ems_support
ipv4
ipv6
ip_type =
public_uid =
private_uid    =
[IM]struct_verno:
[CM]struct_no:
cell_id =
rat type =
VT_SUPPORT =
WFC_SUPPORT =
rat_type:
check_nw_im_cn_signaling_flag =
access RAT =
src IP =
dst IP =
profile.associated_uri =
profile.home_uri       =
profile.imei           =
profile.private_uid    =
profile.public_uid     =
evs_mode_switch =
EVS payload type=
port =
src =
dst =
spi =
ck =
ik =
integrity_algo =
protocol =
encry_algo =
EIMS:

[download]
  • Comment on 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
  • Select or Download Code

Replies are listed 'Best First'.
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

    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;
}

    [download]
    poj
[reply]
[d/l]
[select]
      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;
}

      [download]
[reply]
[d/l]
        
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!

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom