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

I assume this spreadsheet is some kind of template that needs to be filled in otherwise just creating a new one would be much easier. Whilst scanning the spreadsheet for the keywords you need to store the cell address for each if you want to write back the values.

Try this 
#!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;
    
    # 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);
    }
    
  }  
  close $fh;
}
#pp $kwhashref; # debug

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

# update sheet
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
}

# 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]
poj
  • Comment on Re^10: How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column
  • Download Code

Replies are listed 'Best First'.
Re^11: 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 24, 2017 at 18:03 UTC
    Okk .... that's great ....It's working

    I have modified my @files = grep { -f } (<*main_log>); to my @files = grep { -f } (<main_log*>); to Include all the existing main_log files so to search all the keywords. I got Into one Issue ... when one keyword (isim) has 2 values In the files to be searched .. how can I differentiate them Into the same excel .. probable using the 3rd column corresponding to the keyword and putting some text In the comments e.g. isim = 4 In column B with text -> READY_REQ in comments and 1 In column C with text check_sim_type result In commnets of respective cell. .. Is It possible to have differentiation in this way? 

    8116 D: check_sim_type result, usim = 1, isim = 1, ch id = 1,
8116 D send : READY_REQ isim = 4
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

    [download]
[reply]
[d/l]

      Change this sub to shift right existing entries and add comment

      # update sheet
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
}

      [download]

      Add the extra comment parameter into the update_sheet call in 2 places here

          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,$_);
    }

      [download]

      As written it stores the whole line in the comment. I'll leave it to you to filter it as required

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

In Section Seekers of Perl Wisdom