How to delete a line in excel file ???

raghavanv · Oct 14, 2008

Hi All,

Requirement:

i have 4 excel files, each file will have this line

Code:

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

rstat-Ls-20080118-2000.xls

Code:

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

rstat-Ls-20080118-2000.xls (in differnt location)

Code:

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 · Oct 14, 2008

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 · Oct 14, 2008

Look up Spreadsheet:

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[noevil]

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 · Oct 17, 2008

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

Code:

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:

Code:

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

Code:

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 · Oct 17, 2008

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 · Oct 18, 2008

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:

Code:

#!/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 :

Code:

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:

Code:

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 · Oct 20, 2008

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

Code:

#!/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:

Code:

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 · Oct 20, 2008

Hi All,

i got the required result.

Thanks to nitinpes & to every one who guided me.

Working Script:

Code:

#!/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 · Oct 20, 2008

Do you mean

Code:

open(EX,">>$loadavg")

raghavanv · Oct 20, 2008

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 · Oct 20, 2008

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

Code:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to delete a line in excel file ???

raghavanv

Programmer

PinkeyNBrain

IS-IT--Management

travs69

MIS

raghavanv

Programmer

sycoogtit

Programmer

raghavanv

Programmer

raghavanv

Programmer

raghavanv

Programmer

PinkeyNBrain

IS-IT--Management

raghavanv

Programmer

PinkeyNBrain

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor