How to delete a line in excel file ???

[vijayarl]

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

[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

[nithinpes]

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 
     } 
 

[vijayarl]

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

[vijayarl]

hey nitinpes !!! thanks for your reply

i will try 2nd solution what u said ... right away

Regards,
Vijayarl

[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

[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

[nithinpes]

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.

[nithinpes]

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"
 

[vijayarl]

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

[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:

C:\Performance_svap\misc>perl excel.pl
C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
Processing C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls please wa
it
Element DD/MM/YYYY found!
Element HH:MM:SS found!
Element LAVG_1min found!
Element LAVG_5min found!
Element LAVG_15min found!
DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
18/01/2008      18:00:00        0.12    0.46    0.81
18/01/2008      18:00:30        0.16    0.44    0.79
18/01/2008      18:01:00        0.14    0.41    0.77
18/01/2008      18:01:30        0.13    0.38    0.75
18/01/2008      18:02:00        0.12    0.35    0.73
18/01/2008      18:02:30        0.11    0.33    0.71
18/01/2008      18:03:00        0.12    0.31    0.69
18/01/2008      18:03:30        0.11    0.29    0.67
18/01/2008      18:04:00        0.11    0.27    0.65
18/01/2008      18:04:30        0.11    0.26    0.63
18/01/2008      18:05:00        0.13    0.25    0.62
18/01/2008      18:05:30        0.22    0.26    0.61
18/01/2008      18:06:00        0.24    0.26    0.6
18/01/2008      18:06:30        0.21    0.25    0.58
18/01/2008      18:07:00        0.18    0.24    0.57
18/01/2008      18:07:30        0.14    0.23    0.55
18/01/2008      18:08:00        0.13    0.22    0.54
18/01/2008      18:08:30        0.12    0.21    0.52
18/01/2008      18:09:00        0.12    0.2     0.51
18/01/2008      18:09:30        0.12    0.19    0.5
18/01/2008      18:10:00        0.13    0.18    0.48
18/01/2008      18:10:30        0.24    0.21    0.48
18/01/2008      18:11:00        0.26    0.21    0.48
18/01/2008      18:11:30        0.23    0.21    0.47
18/01/2008      18:12:00        0.18    0.2     0.46
18/01/2008      18:12:30        0.15    0.2     0.45
18/01/2008      18:13:00        0.2     0.2     0.44
18/01/2008      18:13:30        0.36    0.23    0.44
18/01/2008      18:14:00        0.45    0.27    0.45
18/01/2008      18:14:30        1.58    0.54    0.54
18/01/2008      18:15:00        1.98    0.75    0.61
18/01/2008      18:15:30        1.38    0.72    0.6
18/01/2008      18:16:00        1.04    0.71    0.6
18/01/2008      18:16:30        0.82    0.68    0.6
18/01/2008      18:17:00        0.54    0.63    0.58
18/01/2008      18:17:30        0.39    0.59    0.57
18/01/2008      18:18:00        0.31    0.55    0.55
18/01/2008      18:18:30        0.29    0.52    0.54
18/01/2008      18:19:00        0.69    0.59    0.57
18/01/2008      18:19:30        0.95    0.67    0.59
18/01/2008      18:20:00        1.16    0.74    0.62
18/01/2008      18:20:30        1.09    0.77    0.63
18/01/2008      18:21:00        1.04    0.79    0.64
18/01/2008      18:21:30        0.77    0.75    0.64
18/01/2008      18:22:00        0.74    0.74    0.64
18/01/2008      18:22:30        0.64    0.71    0.63
18/01/2008      18:23:00        0.43    0.66    0.62
18/01/2008      18:23:30        0.3     0.61    0.6
18/01/2008      18:24:00        0.23    0.56    0.58
18/01/2008      18:24:30        0.2     0.52    0.57
18/01/2008      18:25:00        0.19    0.49    0.55
18/01/2008      18:25:30        0.21    0.46    0.55
18/01/2008      18:26:00        0.21    0.44    0.54
18/01/2008      18:26:30        0.21    0.42    0.52
18/01/2008      18:27:00        0.34    0.43    0.52
18/01/2008      18:27:30        0.36    0.42    0.52
18/01/2008      18:28:00        0.32    0.41    0.51
18/01/2008      18:28:30        0.47    0.43    0.52
18/01/2008      18:29:00        0.68    0.49    0.53
18/01/2008      18:29:30        0.93    0.57    0.55
18/01/2008      18:30:00        0.87    0.59    0.56
18/01/2008      18:30:30        0.99    0.64    0.58
18/01/2008      18:31:00        0.91    0.66    0.59
18/01/2008      18:31:30        0.92    0.68    0.6
18/01/2008      18:32:00        0.84    0.68    0.61
18/01/2008      18:32:30        1.07    0.75    0.63
18/01/2008      18:33:00        1.15    0.8     0.65
18/01/2008      18:33:30        1.04    0.81    0.66
18/01/2008      18:34:00        1.29    0.89    0.69
18/01/2008      18:34:30        1.47    0.97    0.72
18/01/2008      18:35:00        1.12    0.93    0.72
18/01/2008      18:35:30        1.33    1       0.75
18/01/2008      18:36:00        1.5     1.08    0.79
18/01/2008      18:36:30        1.53    1.12    0.81
18/01/2008      18:37:00        1.44    1.14    0.82
18/01/2008      18:37:30        1.54    1.2     0.86
18/01/2008      18:38:00        1.77    1.28    0.89
18/01/2008      18:38:30        1.71    1.32    0.92
18/01/2008      18:39:00        1.61    1.32    0.93
18/01/2008      18:39:30        1.86    1.41    0.98
18/01/2008      18:40:00        1.68    1.41    0.99
18/01/2008      18:40:30        1.81    1.46    1.02
18/01/2008      18:41:00        2.05    1.55    1.07
18/01/2008      18:41:30        1.99    1.59    1.09
18/01/2008      18:42:00        2.14    1.66    1.14
18/01/2008      18:42:30        2.08    1.69    1.16
18/01/2008      18:43:00        2.15    1.75    1.2
18/01/2008      18:43:30        2.09    1.77    1.23
18/01/2008      18:44:00        2.11    1.8     1.25
18/01/2008      18:44:30        2.1     1.83    1.28
18/01/2008      18:45:00        2.18    1.88    1.32
18/01/2008      18:45:30        2.23    1.92    1.35
18/01/2008      18:46:00        2.14    1.93    1.37
18/01/2008      18:46:30        2.2     1.96    1.4
18/01/2008      18:47:00        2.23    1.99    1.43
18/01/2008      18:47:30        2.42    2.06    1.47
18/01/2008      18:48:00        2.62    2.14    1.52
18/01/2008      18:48:30        2.67    2.2     1.56
18/01/2008      18:49:00        2.58    2.23    1.59
18/01/2008      18:49:30        2.46    2.23    1.61
18/01/2008      18:50:00        2.44    2.25    1.64
18/01/2008      18:50:30        2.35    2.25    1.65
18/01/2008      18:51:00        2.3     2.24    1.67
18/01/2008      18:51:30        2.28    2.25    1.69
18/01/2008      18:52:00        2.57    2.31    1.73
18/01/2008      18:52:30        2.69    2.37    1.77
18/01/2008      18:53:00        2.75    2.41    1.8
18/01/2008      18:53:30        2.83    2.47    1.84
18/01/2008      18:54:00        2.77    2.48    1.87
18/01/2008      18:54:30        3       2.57    1.92
18/01/2008      18:55:00        2.97    2.6     1.95
18/01/2008      18:55:30        3.08    2.66    1.99
18/01/2008      18:56:00        3.05    2.7     2.02
18/01/2008      18:56:30        2.98    2.71    2.05
18/01/2008      18:57:00        3.12    2.77    2.09
18/01/2008      18:57:30        2.86    2.74    2.11
18/01/2008      18:58:00        2.79    2.74    2.12
18/01/2008      18:58:30        2.75    2.73    2.14
18/01/2008      18:59:00        3.09    2.81    2.19
18/01/2008      18:59:30        3.21    2.88    2.23
 

Regards,
Vijayarl

[nithinpes]

For writing to excel file, you may make use of Spreadsheet::WriteExcel module.
-Nithin

[nithinpes]

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"; 
 

[vijayarl]

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

[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

[nithinpes]

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..

[vijayarl]

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

[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