How to delete a line in excel file ???

[raghavanv]

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

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,
Raghavanv

 

[PinkeyNBrain]

Have you worked with Win32:OLE ?
-or-
How versed are you with VB under excel?

Win32:OLE can get a lot of the work done without working too directly with excel macros. For reference check out "Win32 Perl Programming: The Standard Extensions". I was successful using examples found under "

http://omaha.pm.rg/emails/1998-2002/msg00021.html"

- Don't know if the site is still available though.

If you're more comfortable with excel macros, create one that will read your target excel file and dump it to a .txt file, then use perl to read that in and process. Use 2nd macro to read in processed data and dump back into excel format.

There are probably better ways to do this, but this is at least one way.

 

[travs69]

Look up Spreadsheet:arseExcel and Spreadsheet::WriteExcel on cpan.. or just do a search for Excel

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[raghavanv]

Hi All,


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,
Raghavanv

 

[sycoogtit]

If you have an array @a in perl(< 6), then you access the ith element with $a[$i] instead of @a[$i]. That's all the warning is saying. I think that changes with perl6...

I've never used Spreadsheet:arseExcel::Simple, but I think you can check the value of the LAVG_1min column with $data[2].

--

http://MyUniversityTutor.com

 

[raghavanv]

Thanks sycoogtit for your reply..

i got almost the expected result(thanks to nitinpes !!!) but now i stuck with this.. not getting what is the error
full 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 @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";
			   }
			   open(EX,">C:\\Performance_svap\\INPUT_FILES\\my_result.xls") or die "error:$!";
			   #print EX "$output[0]\t$output[1]\t$output[2]\t$ouput[3]\t$output[4]\n";
			   print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";  
			#}   
     }
     }
	 
	 #print "@outfile\n";
  }
 }

Error what am getting :

use of uninitialized value in concatenation (.) or string at excel.pl line 56.

All i need is to put the required values into another excel file.
output i got in cmd prompt:

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

All i need is to write this output into another excel file.

Can anyone help me out ?

Note: sorry for indentation as i use notepad++ it goes fine there but when i put the script here it goes out..don't know what to do on this ?

Regards,
Raghavanv

 

[raghavanv]

Hi All,

I done with almost with the required result expect for now am getting only one line in the ouput file

anyone will help me out ???

#!/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;		
				}
			}
		}
		}
	}  
}

ouput file look like this:

DD/MM/YYYY  HH:MM:SS  LAVG_1min  LAVG_5min  LAVG_15min
18/01/2008  18:59:30     3.21      2.88      2.23

script writes only last required data into output file

Regards,
Raghavanv

 

[raghavanv]

Hi All,

i got the required result.

Thanks to nitinpes & to every one who guided me.

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 again !!!!

Regards,
Raghavanv

 

[PinkeyNBrain]

Do you mean

open(EX,">>$loadavg")

 

[raghavanv]

hi PinkeyNBrain,

i didn't get you ???

Anyway's am not trying to open a excel & append data into it.
i just want to create a new excel file & put all the required data present in the array form (array element)into it.


Anyway's now i got the expected result.

Regards,
Vijayarl

 

[PinkeyNBrain]

Just to close the loop here - One of your posts indicated that you were receiving only the last line of our output. Seeing the coding construct

 - start loop
   - open file with ">" (create/overwrite operator)
   - write to file
 - end of loop

The above will effectively only save the last iteration of the loop. Using ">>" will append into your file. Your solution of moving the 'open' command outside the loop works as well....note however that you're still looping over 'foreach sheet' without changing your output file name and will again encounter data loss if you're not careful.