Hi All,
Requirement:
i have 4 excel files, each file will have this line -
18/01/2008 20:00:00 5.31 5.48 5.33
-
i need to search for this line & extract this line then print it another excel file.
INPUT file look like this:
Filename:prstat-Ls-20080118-2000.xls -
DD/MM/YYYY HH:MM:SS PID USERNAME LAVG_1min LAVG_5min LAVG_15min
-
18/01/2008 20:00:00 24992 vappr
-
18/01/2008 20:00:00 24989 vappr
-
18/01/2008 20:00:00 24992 vappr
-
18/01/2008 20:00:00 24989 vappr
-
18/01/2008 20:00:00 24990 vappr
-
18/01/2008 20:00:00 24989 vappr
-
18/01/2008 20:00:00 5.31 5.48 5.33
-
18/01/2008 20:00:00 24989 vappr
-
18/01/2008 20:00:00 24990 vappr
-
18/01/2008 20:00:00 24989 vappr
-
18/01/2008 20:00:30 3.31 1.48 7.33
-
OUTPUT file should look like this:
Filename:prstat-Ls-20080118-2000.xls (in differnt location) -
DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
-
18/01/2008 20:00:00 5.31 5.48 5.3
-
18/01/2008 20:00:30 3.31 1.48 7.33
-
i haven't started scripting for this, as i have struck on how to gohead with this.
first i thought read the file line by line then delete the line till i get some values in LAVG_1min but this won't give me the required output
Is this a good idea to go head ???
can anyone help me on how to do this ??
hope i think i have made requirements clear !!!!
Regards,
Vijayarl
17 6185
Hi Everyone,
going forward ,i did some scripting to read the data from the excel file & put the data into an array.
(used Spreadsheet-ParseExcel-Simple-1.04 module from CPAN)
full script: it just reads the data & puts them into array -
use strict;
-
#use Spreadsheet::ParseExcel;
-
use Spreadsheet::ParseExcel::Simple;
-
-
my $oExcel = new Spreadsheet::ParseExcel;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
foreach my $f (@file){
-
print "$f\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet ($xls->sheets) {
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
print "@data[0]\n";
-
}
-
}
-
}
-
i have few doubts can anyone explain me the 2nd line from this :
1.first question: -
C:\Performance_svap\misc>perl parse-excel.pl
- Scalar value @data[0] better written as $data[0] at parse-excel.pl line 16
-
C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
-
i just wanted to check if first element of the array holds what value & if it matches this data then put into another array then later write into another excel file -
DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
-
my second question:
how can i fetch the values present under LAVG_1min column ie,
keep reading the line & check if LAVG_1min has value if so, then fetch the respective DD/MM/YYYY,HH:MM:SS,LAVG_5min & LAVG_15min values ???
hope not asking too much.. just need help to achive this !!!!
Regards,
Vijayarl
Hi Everyone,
going forward ,i did some scripting to read the data from the excel file & put the data into an array.
(used Spreadsheet-ParseExcel-Simple-1.04 module from CPAN)
full script: it just reads the data & puts them into array -
use strict;
-
#use Spreadsheet::ParseExcel;
-
use Spreadsheet::ParseExcel::Simple;
-
-
my $oExcel = new Spreadsheet::ParseExcel;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
foreach my $f (@file){
-
print "$f\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet ($xls->sheets) {
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
print "@data[0]\n";
-
}
-
}
-
}
-
i have few doubts can anyone explain me the 2nd line from this :
1.first question: -
C:\Performance_svap\misc>perl parse-excel.pl
- Scalar value @data[0] better written as $data[0] at parse-excel.pl line 16
-
C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
-
i just wanted to check if first element of the array holds what value & if it matches this data then put into another array then later write into another excel file -
DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
-
my second question:
how can i fetch the values present under LAVG_1min column ie,
keep reading the line & check if LAVG_1min has value if so, then fetch the respective DD/MM/YYYY,HH:MM:SS,LAVG_5min & LAVG_15min values ???
hope not asking too much.. just need help to achive this !!!!
Regards,
Vijayarl
For the first question, the appropriate way of accessing an element in an array is to use $data[0] rather than @data[0] as it is a scalar data.
For the second question, the LAVG_1min column happens to be 5th element in the array. So, you can check for existence of the 5th element and fetch/print the array: -
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found
-
}
-
ok.. now what i did is to search for the required header & put them under one array. -
#!/usr/bin/perl -w
-
use strict;
-
use Spreadsheet::ParseExcel::Simple;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
my @output;
-
foreach my $f (@file){
-
print "$f\n";
-
print "Processing $f please wait\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet ($xls->sheets) {
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
my $data = 'DD/MM/YYYY';
-
if (grep {$_ eq $data} @data) {
-
print "Element $data found!\n" ;
-
# Add one element at the end of the array
-
push(@output, $data);
-
}
-
my $hr = 'HH:MM:SS';
-
if (grep {$_ eq $hr} @data) {
-
print "Element $hr found!\n" ;
-
push(@output, $hr);
-
}
-
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
print "Element $la1 found!\n" ;
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
print "Element $la5 found!\n" ;
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
print "Element $la15 found!\n" ;
-
push(@output, $la15);
-
}
-
}
-
print "@output\n";
-
}
-
}
-
now all i need to to traverse through excel file till i get a value under LAVG_1min column, if so, then reading the content.
can anyone help me how to traverse the file & fetch a reqiured value ???
Is there any better way to do this, as i know this is not best way to script : -
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
print "Element $la1 found!\n" ;
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
print "Element $la5 found!\n" ;
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
print "Element $la15 found!\n" ;
-
push(@output, $la15);
-
}
-
any help plz ??
Regards,
Vijayarl
hey nitinpes !!! thanks for your reply
i will try 2nd solution what u said ... right away
Regards,
Vijayarl
hai nitinpes,
i did try out you way
but it prints all the data present in the excel file
ouput what i got after implementing what u said: -
18/01/2008 18:36:00 133 root 4560K 816K sleep 59 0 0:00:00 0.0% picld/1
-
18/01/2008 18:36:00 58 root 13M 5784K sleep 59 0 0:00:15 0.0% vxconfigd/1
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/53
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/52
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/51
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/50
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/49
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/48
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/47
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:02 0.0% java/46
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/45
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/44
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/43
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/42
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/41
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/40
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 6 17 0:00:00 0.0% java/38
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/37
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/36
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:02 0.0% java/35
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:07 0.0% java/34
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:06 0.0% java/33
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:07 0.0% java/32
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/31
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 57 0 0:00:02 0.0% java/30
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/29
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:01 0.0% java/28
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/27
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/26
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/25
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:17:03 0.0% java/22
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:44 0.0% java/20
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:06:47 0.0% java/19
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:15:44 0.0% java/18
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:06:39 0.0% java/17
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:08:55 0.0% java/16
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:57 0.0% java/15
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:08:35 0.0% java/14
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:54 0.0% java/13
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:22 0.0% java/12
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/11
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/10
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/9
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:10 0.0% java/8
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 28 10 0:00:18 0.0% java/7
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/6
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/5
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 51 2 0:00:01 0.0% java/4
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/3
-
18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:53 0.0% java/1
-
18/01/2008 18:36:00 627 daemon 2816K 856K sleep 59 0 0:00:00 0.0% rpcbind/1
-
18/01/2008 18:36:00 1.5 1.08 0.79
-
18/01/2008 18:36:30 24992 vappr 1217M 1044M sleep 21 10 0:15:37 1.7% java/23
-
18/01/2008 18:36:00 1.5 1.08 0.79
-
i just want this line to be extracted from the above ouput : -
18/01/2008 18:36:00 1.5 1.08 0.79
-
Regards,
Vijayarl
nitinpes,
i got the problem !!! there was slight error in this: -
print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found
-
it has to be : -
print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found
-
now, i got the all the values under LAVG_1min column header & i guess i can fetch other required cloumn values.
now last part : how will i print the data in this format: -
DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
-
18/01/2008 18:00:00 0.12 0.46 0.52
-
18/01/2008 18:00:30 0.09 0.21 0.79
-
18/01/2008 18:01:00 0.69 0.32 0.66
-
Regards,
Vijayarl
That should have given you an idea of printing only the required elements. I had printed entire array. Replace: -
print "@data\n" if(exists $data[4]);
-
with: -
print "$data[0]\t$data[1]\t$data[4]\t$data[5]\t$data[6]\n" if(exists $data[4]);
-
Also, remember that index values are based on the sample format that you provided. If the file content is different, use index accordingly.
nitinpes,
i got the problem !!! there was slight error in this: -
print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found
-
it has to be : -
print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found
-
As I said, the index value that I used is based on the data that you provided. You should be knowing at which index , LAVG_1min value resides , in the array that you extracted from file.
You may use the following OR as appropriate: -
print "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"
-
thanks nitinpes for your kind reply...
now i am almost getting the near the required result
i ahve try to get the required data but problem ia with the DD/MM/YYYY & HH:MM:SS column data
as soon as i get the value under LAVG_1min i need to fetch values of date & time from the same row.
now what am getting is all the date & time in the output -
#!/usr/bin/perl -w
-
use strict;
-
use Spreadsheet::ParseExcel::Simple;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
my @output;my @outfile;
-
foreach my $f (@file){
-
print "$f\n";
-
print "Processing $f please wait\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet ($xls->sheets) {
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
my $data = 'DD/MM/YYYY';
-
if (grep {$_ eq $data} @data) {
-
print "Element $data found!\n" ;
-
# Add one element at the end of the array
-
push(@output, $data);
-
}
-
my $hr = 'HH:MM:SS';
-
if (grep {$_ eq $hr} @data) {
-
print "Element $hr found!\n" ;
-
push(@output, $hr);
-
}
-
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
print "Element $la1 found!\n" ;
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
print "Element $la5 found!\n" ;
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
print "Element $la15 found!\n" ;
-
push(@output, $la15);
-
}
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
#print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found
-
foreach my $m (@data){
-
if(exists $data[12]){
-
push(@outfile,$data[0]);
-
push(@outfile,$data[1]);
-
push(@outfile,$data[12]);
-
push(@outfile,$data[13]);
-
push(@outfile,$data[14]);
-
}
-
}
-
}
-
}
-
print "@output\n";
-
print "@outfile\n";
-
}
-
}
-
now i want to know how to traverse rowwise & fetch the required data ???
plz help me out
Regards,
Vijayarl
Thanks Nitinpes for your help !!!!
i got the desired result,now all i want is how to put this results into another excel file. as there will be more excel files need to read from the dir. -
#!/usr/bin/perl -w
-
use strict;
-
use Spreadsheet::ParseExcel::Simple;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
my @output;my @outfile;
-
foreach my $f (@file){
-
print "$f\n";
-
print "Processing $f please wait\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet ($xls->sheets) {
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
my $data = 'DD/MM/YYYY';
-
if (grep {$_ eq $data} @data) {
-
print "Element $data found!\n" ;
-
# Add one element at the end of the array
-
push(@output, $data);
-
}
-
my $hr = 'HH:MM:SS';
-
if (grep {$_ eq $hr} @data) {
-
print "Element $hr found!\n" ;
-
push(@output, $hr);
-
}
-
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
print "Element $la1 found!\n" ;
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
print "Element $la5 found!\n" ;
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
print "Element $la15 found!\n" ;
-
push(@output, $la15);
-
}
-
print "@output\n";
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
#print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found
-
#foreach my $m (@data){
-
if(exists $data[12]){
-
push(@outfile,$data[0]);
-
push(@outfile,$data[1]);
-
push(@outfile,$data[12]);
-
push(@outfile,$data[13]);
-
push(@outfile,$data[14]);
-
print "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";
-
}
-
#}
-
}
-
}
-
-
#print "@outfile\n";
-
}
-
}
-
output i got from this:
Regards,
Vijayarl
If you want to print only the contents of the array to new file you may simply open an excel sheet for writing : -
open(EX,">my_result.xls") or die "error:$!"
-
and print the required data to the file: -
print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";
-
Hi nitinpes,
i get this error when i try to run : -
C:\Performance_svap\misc>perl excel.pl
-
String found where operator expected at excel.pl line 55, near "EX "$data[0]\t$d
-
ata[1]\t$data[12]\t$data[13]\t$data[14]\n""
-
(Do you need to predeclare EX?)
-
syntax error at excel.pl line 55, near "print"
-
Execution of excel.pl aborted due to compilation errors.
-
line 54 is about writing into a another excel file: -
open(EX,'>C:\Performance_svap\INPUT_FILES\my_result.xls') or die "error:$!"
-
print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"; #### line 54###
-
Regards,
Vijayarl
Ahhh !!! sorry there was no semicolon at the end of open stmt....silly mistake..
anyway's now this error, what it actually means: -
-
use of uninitialized value in concatenation (.) or string at excel.pl line 56.
-
-
Regards,
Vijayarl
For some iterations the variables are not getting the values.
May be some data in the file are not having the format that you are expecting. Try to figure that out and skip such lines.....
Put some effort and try to fix the problem before you ask again..
Thanks nitinpes..... thanks for your patience reply...
i have done almost expect for now, in the ouput file i get only last entry of data before in cmd prompt i got all the required data but now only one entry.
really not getting what mistake i have made...:-(
am not so good at programming so am asking help..
can anyone help me ??? -
#!/usr/bin/perl -w
-
use strict;
-
use Spreadsheet::ParseExcel::Simple;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
my @output;
-
my $loadavg;
-
#my @outfile;
-
my $str;
-
my $str1;
-
my @data;
-
my $date;
-
foreach my $f (@file){
-
$str = substr($f,32,39);
-
$str1 = substr($str,0,9);
-
$loadavg ="c:\\Performance_svap\\OUTPUT_FILES\\$str";
-
print "$loadavg\n";
-
if($str1 =~/prstat-Ls/){
-
print "$f\n";
-
print "Processing $f please wait\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet($xls->sheets) {
-
while ($sheet->has_data) {
-
@data = $sheet->next_row;
-
my $date = 'DD/MM/YYYY';
-
if (grep {$_ eq $date} @data) {
-
push(@output, $date);
-
}
-
my $hr = 'HH:MM:SS';
-
if (grep {$_ eq $hr} @data) {
-
push(@output, $hr);
-
}
-
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
push(@output, $la15);
-
}
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
if(exists $data[12]){
-
open(EX,">$loadavg") or die "Can't open open $loadavg:$!";
-
print EX "$output[0]\t$output[1]\t$output[2]\t$output[3]\t$output[4]\n";
-
print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";
-
close EX;
-
}
-
}
-
}
-
}
-
}
-
}
-
thanks & regards,
Vijayarl
hi All,
i got the required result.
Thanks to nitinpes !!! i was almost given up... he came & gave me the right inputs..
Working Script: -
#!/usr/bin/perl -w
-
use strict;
-
use Spreadsheet::ParseExcel::Simple;
-
my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
-
my @file=glob("$dir");
-
my @output;
-
my $loadavg;
-
my $str;
-
my $str1;
-
my @data;
-
my $date;
-
foreach my $f (@file){
-
$str = substr($f,32,39);
-
$str1 = substr($str,0,9);
-
$loadavg ="c:\\Performance_svap\\OUTPUT_FILES\\$str";
-
if($str1 =~/prstat-Ls/){
-
print "$f\n";
-
print "Processing $f please wait\n";
-
my $xls = Spreadsheet::ParseExcel::Simple->read($f);
-
foreach my $sheet($xls->sheets) {
-
while ($sheet->has_data) {
-
@data = $sheet->next_row;
-
my $date = 'DD/MM/YYYY';
-
if (grep {$_ eq $date} @data) {
-
push(@output, $date);
-
}
-
my $hr = 'HH:MM:SS';
-
if (grep {$_ eq $hr} @data) {
-
push(@output, $hr);
-
}
-
my $la1 = 'LAVG_1min' ;
-
if (grep {$_ eq $la1} @data) {
-
push(@output, $la1);
-
}
-
my $la5 = 'LAVG_5min' ;
-
if (grep {$_ eq $la5} @data) {
-
push(@output, $la5);
-
}
-
my $la15 = 'LAVG_15min' ;
-
if (grep {$_ eq $la15} @data) {
-
push(@output, $la15);
-
}
-
open(EX,">$loadavg") or die "Can't open open $loadavg:$!";
-
print EX "$output[0]\t$output[1]\t$output[2]\t$output[3]\t$output[4]\n";
-
while ($sheet->has_data) {
-
my @data = $sheet->next_row;
-
if(exists $data[12]){
-
print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";
-
}
-
}
-
}
-
close EX;
-
}
-
}
-
}
-
-
Thanks to all...
Regards,
Vijayarl
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
1 post
views
Thread by smonczka |
last post: by
|
7 posts
views
Thread by Smitty |
last post: by
|
2 posts
views
Thread by Bob |
last post: by
|
8 posts
views
Thread by DavidB |
last post: by
|
1 post
views
Thread by Murali via DotNetMonster.com |
last post: by
|
3 posts
views
Thread by Rene |
last post: by
| | | | | | | | | | | | | |