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 6406
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: smonczka |
last post by:
I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than...
|
by: Smitty |
last post by:
I have a function that imports an Excel file into an Access table using SQL.
I then close the OleDataReader and the OleDbConnection, then dispose the
OleDbCommand, then OleDbConnection. The calling...
|
by: Bob |
last post by:
In the following code, when the DELETE line is executed I get a dialog asking if I want to delete or to cancel. How do I turn it off? Also when the first CLOSE line is executed, the application so...
|
by: DavidB |
last post by:
I'm trying to delete a file with
File.Delete("c:\Documents and Settings\%username%\Application
Data\Microsoft\Excel\excel*.xlb")
On doing this I get an Exception "Illegal characters in path" -...
|
by: Murali via DotNetMonster.com |
last post by:
Hi,
I am uploading the excel file to the server and reading the data and
dumping in to sqlserver.
Now i want to delete the excel file from the server, but i am getting a error
like "file in...
|
by: Rene |
last post by:
Hi Proggies,
i try to delete an excel worksheet but nothing happens (work on vb.net 2005)
.....
xlSheet = xlApp.Sheets("Master")
xlSheet.select
xlSheet.delete
also try
|
by: SteveBark |
last post by:
Hello all
I am currently trying to develop a script that will take a value from an Excel spreadsheet cell and use that to run a query against an Access table to delete all rows that match the...
|
by: Bxitty |
last post by:
Hello all,
I am trying the below code to delete a worksheet using VB.NET..I amgeeting an error with the delete...any idea where this could be wrong..
Dim str_path As String
Dim...
|
by: Annalyzer |
last post by:
My database must produces a .csv file with a header line that is different from the detail lines. This .csv file contains a monthly report to an outside agency and is uploaded online so it must...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |