CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column

 



rocky123
Novice

Feb 23, 2017, 12:05 AM

Post #1 of 11 (2420 views)
How to read the value of a variable (read from a Excel file converted Into text file) and put then back into Excel column Can't Post

Issue :

I am able to read the variables from excel and print them in the above program
but I want to read these variables(from Variables to read from Excel sheet :) value from Input data (from My input data to search from:) and put them back into excel sheet which I am not able to do. can some one pls help on the same ?


Quote
Variables to read from Excel sheet
isim =
usim =
sim_invalid =
pcscf_discovery_flag
signaling_flag
ran_type
emergency_ind
rat
pcscf_v4
pcscf_v6
plmn
is_ems_support
ipv4
ipv6



Quote
My input data to search from:

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


MY program:

Code
#use strict; 
#use 5.010;
use Spreadsheet::Read qw(ReadData);
my $book = ReadData ('UE_NW_Parameters.xlsx');
#my @files = grep { -f } (<*.txt>,<*main_log>,<*Project>,<*properties>);
my @files = grep { -f } (<*main_log>);
my @rows = Spreadsheet::Read::rows($book->[1]);
foreach my $i (1 .. scalar @rows) {
foreach my $j (1 .. scalar @{$rows[$i-1]}) {
my $temp = "\n$rows[$i-1][$j-1]";
my @keywords = "$temp";
print @keywords;
}
foreach my $file (@files){
open(my $fh, '<', $file) or die $!;
my @content = <$fh>;
close($fh);
my $l = 0;

foreach my $kw (@keywords)
{
foreach (@content){
$l++;
my $search = $kw;
if ($search =~ /(\ d+)/)
{
printf 'Found keyword %s in file %s, line %d:%s'.$/, $search, $file, $l, $_;
last;
}
}
}
}

}



Laurent_R
Veteran / Moderator

Feb 23, 2017, 6:42 AM

Post #2 of 11 (2417 views)
Re: [rocky123] 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 reply to] Can't Post

Hi rocky123,

I don't understand the relation between your description of the task at hand (something seemingly simple) and the code that you're showing, which appears to be very complicated (with four foreach loops) for this task.

At this point, I do not really understand what your code intends to do.

Perhaps you could show what output you expect from the input you've shown. With that, someone here might be able to suggest something simpler.

One note on your code despite my lack of understanding thereof. This:

Code
        my $temp = "\n$rows[$i-1][$j-1]";  
my @keywords = "$temp";
print @keywords;
}

is most probably not doing what you want, because the @keywords array is reset to empty (and then to the value of "$temp") each time you go through the loop. You probably want to declare @keywords before entering the loop, and also to push
"$temp" onto it, rather than assigning your array to this string value.


FishMonger
Veteran / Moderator

Feb 23, 2017, 6:54 AM

Post #3 of 11 (2416 views)
Re: [rocky123] 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 reply to] Can't Post

Why did you comment out the use strict; statement and why are you not using the warnings pragma? Putting on a blindfold doesn't help you to drive a car so why would put on a blindfold when writing code?

How do you expect us to test your code when you didn't provide the required speadsheet input file? You should include it in your post as an attachment.

In what way does your script fail to do what you want?

What errors and warnings are you receiving (after adding in the strict and warnings pragmas)?


rocky123
Novice

Feb 23, 2017, 9:34 AM

Post #4 of 11 (2411 views)
Re: [Laurent_R] 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 reply to] Can't Post

@Laurent .. thanks ..can you please help me In the following?

Quote
I have modified the code and now I am able to do the following :

1. Extract the data from Excel file Into text file as keywords -> OK
2. I want to search these keywords from list of files In a folder (main_log files here) but the output generated Is quite weird -> Not Ok
3. From these output generated (i.e. matched lines containing the keywords) I want to pull out the values of said keywords or Variables -> Not working

P.S. If I remove the #strict & #warnings I am getting a weird warning message as well.



Code
MY program 
#!/usr/bin/env perl
#use strict;
#use warnings;
my $ex = '';
my $book = '';
my $sheet = '';
my $data = '';
my $value = 0;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}

$book = $ex->Workbooks->Open("E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\\Excel\\UE_NW_Parameters.xlsx");
$sheet = $book->Worksheets(1);

$data = $sheet -> Range("A1:B2873") -> {Value};

# Opening the Output file for exporting the Excel data Into Text File
open(FILE, ">excel_data.txt");
foreach my $cell_ref (@$data) {
foreach $value (@$cell_ref){
print FILE ($value);
}
print FILE "\n";
}
close FILE;


# Opening excel data File here
open( my $kw, '<', 'excel_data.txt') or die $!;
my @keywords = <$kw>;
chomp(@keywords); # remove newlines at the end of keywords



#get list of files in current directory
my @files = <main_log*>;

# loop over each file to search keywords in
foreach my $file (@files)
{
open(my $fh, '<', $file) or die $!;
my @content = <$fh>;
close($fh);
my $l = 0;
my $outfile = 'excel_match'.$file.'.txt';
print "Check the output generated In file $outfile\n";
open my $fh_out, '>', $outfile or die "$!";

foreach my $kw (@keywords)
{
my $search = quotemeta($kw); # otherwise keyword is used as regex, not literally
foreach (@content)
{ # go through every line for this keyword
$l++;
if (/\Q$search/)
{
printf $fh_out "%s in file %s, line %d:%s'.$/, $kw, $file, $l, $_";
last;
}
}
}
}



rocky123
Novice

Feb 23, 2017, 9:39 AM

Post #5 of 11 (2410 views)
Re: [FishMonger] 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 reply to] Can't Post

@FishMonger .. After printing Strict & warning I am getting weird error on Line#38 of my below program.
The excel data I have already Added In my question In form of quote.
In my latest code posted , I am not able to get the matched lines according to the keywords i.e. variables from excel file. Can you pls help ?


Quote
isim =
usim =
sim_invalid =
pcscf_discovery_flag
signaling_flag
ran_type
emergency_ind
rat
pcscf_v4
pcscf_v6
plmn
is_ems_support
ipv4
ipv6


Code
 
#!/usr/bin/env perl
#use strict;
#use warnings;
my $ex = '';
my $book = '';
my $sheet = '';
my $data = '';
my $value = 0;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}

$book = $ex->Workbooks->Open("E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\\Excel\\UE_NW_Parameters.xlsx");
$sheet = $book->Worksheets(1);

$data = $sheet -> Range("A1:B2873") -> {Value};

# Opening the Output file for exporting the Excel data Into Text File
open(FILE, ">excel_data.txt");
foreach my $cell_ref (@$data) {
foreach $value (@$cell_ref){
print FILE ($value);
}
print FILE "\n";
}
close FILE;


# Opening excel data File here
open( my $kw, '<', 'excel_data.txt') or die $!;
my @keywords = <$kw>;
chomp(@keywords); # remove newlines at the end of keywords



#get list of files in current directory
my @files = <main_log*>;

# loop over each file to search keywords in
foreach my $file (@files)
{
open(my $fh, '<', $file) or die $!;
my @content = <$fh>;
close($fh);
my $l = 0;
my $outfile = 'excel_match'.$file.'.txt';
print "Check the output generated In file $outfile\n";
open my $fh_out, '>', $outfile or die "$!";

foreach my $kw (@keywords)
{
my $search = quotemeta($kw); # otherwise keyword is used as regex, not literally
foreach (@content)
{ # go through every line for this keyword
$l++;
if (/\Q$search/)
{
printf $fh_out "%s in file %s, line %d:%s'.$/, $kw, $file, $l, $_";
last;
}
}
}
}



FishMonger
Veteran / Moderator

Feb 23, 2017, 9:45 AM

Post #6 of 11 (2408 views)
Re: [rocky123] 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 reply to] Can't Post

Sorry, I'm not willing to help until you enable the pragmas. They are telling you about problems in your code that need to be fixed. Ignoring those problems is not going to do any good.


rocky123
Novice

Feb 23, 2017, 9:52 AM

Post #7 of 11 (2405 views)
Re: [FishMonger] 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 reply to] Can't Post

I have enabled Strict & warning In my below program but I am getting the below warnings now

Quote
Use of uninitialized value $value in print at E:\Automation Related\Perl Scripts
- ALL\IMS_debugging\Excel\Excel_9.pl line 29.


My program :

Code
#!/usr/bin/env perl 
use strict;
use warnings;
my $ex = '';
my $book = '';
my $sheet = '';
my $data = '';
my $value = 0;

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}

$book = $ex->Workbooks->Open("E:\\Automation Related\\Perl Scripts - ALL\\IMS_debugging\\Excel\\UE_NW_Parameters.xlsx");
$sheet = $book->Worksheets(1);

$data = $sheet -> Range("A1:B2873") -> {Value};

# Opening the Output file for exporting the Excel data Into Text File
open(FILE, ">excel_data.txt");
foreach my $cell_ref (@$data) {
foreach $value (@$cell_ref){
print FILE ($value);
}
print FILE "\n";
}
close FILE;


# Opening excel data File here
open( my $kw, '<', 'excel_data.txt') or die $!;
my @keywords = <$kw>;
chomp(@keywords); # remove newlines at the end of keywords



#get list of files in current directory
my @files = <main_log*>;

# loop over each file to search keywords in
foreach my $file (@files)
{
open(my $fh, '<', $file) or die $!;
my @content = <$fh>;
close($fh);
my $l = 0;
my $outfile = 'excel_match'.$file.'.txt';
print "Check the output generated In file $outfile\n";
open my $fh_out, '>', $outfile or die "$!";

foreach my $kw (@keywords)
{
my $search = quotemeta($kw); # otherwise keyword is used as regex, not literally
foreach (@content)
{ # go through every line for this keyword
$l++;
if (/\Q$search/)
{
print $fh_out "%s in file %s, line %d:%s'.$/, $kw, $file, $l, $_";
last;
}
}
}
}



FishMonger
Veteran / Moderator

Feb 23, 2017, 10:15 AM

Post #8 of 11 (2401 views)
Re: [rocky123] 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 reply to] Can't Post

The warning is telling you that $value is empty/undefined. That would happen if there was no value in the cell you're looping over. I can't duplicate the problem because you haven't supplied the spreadsheet. A copy/past of its content is not sufficient.


rocky123
Novice

Feb 23, 2017, 10:18 AM

Post #9 of 11 (2399 views)
Re: [FishMonger] 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 reply to] Can't Post

Sorry .. I am attaching the excel sheet of my keywords here. Thanks.
Attachments: UE_NW_Parameters.xlsx (12.0 KB)


rocky123
Novice

Feb 23, 2017, 10:42 AM

Post #10 of 11 (2392 views)
Re: [rocky123] 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 reply to] Can't Post

I want my output as an excel sheet (attached). My example Input data Is


Quote
My input data to search from:

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

Attachments: UE_NW_Parameters_output.xlsx (12.1 KB)


FishMonger
Veteran / Moderator

Feb 24, 2017, 5:28 PM

Post #11 of 11 (2381 views)
Re: [rocky123] 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 reply to] Can't Post

I've never used Win32::OLE, so I'll need to experiment with it. I normally use the the other spreadsheet modules so I can write platform independent scripts.

If I'm not too busy at work tomorrow, I'll try to run some tests.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives