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

Using keyword isim = as an example, do you want the value 1 to be inserted in the column next to that keyword => YES Update: You want output to be ran_type 0 pcscf_v4 10.56.5.85 =>YES pcscf_v4 : num = 2, protocol_type = 0x21, port_num = 0, addr = 10.56.5.85 the match is against keyword addr = => actually there Is also a field address as well but I want to capture only addr corresponding pcscf_v4. My 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
At stop time, parameter address=noping

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

Replies are listed 'Best First'.
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
by poj (Abbot) on Feb 24, 2017 at 08:23 UTC

    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
    Read more... (3 kB)
    poj
[reply]
[d/l]
      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]

In Section Seekers of Perl Wisdom