By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,970 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > perl > questions > how to delete a line in excel file ???
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

How to delete a line in excel file ???

P: 65
Hi All,

Requirement:

i have 4 excel files, each file will have this line
Expand|Select|Wrap|Line Numbers
  1. 18/01/2008 20:00:00            5.31    5.48    5.33
  2.  
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
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY HH:MM:SS PID USERNAME LAVG_1min LAVG_5min LAVG_15min
  2. 18/01/2008 20:00:00 24992 vappr        
  3. 18/01/2008 20:00:00 24989 vappr        
  4. 18/01/2008 20:00:00 24992 vappr        
  5. 18/01/2008 20:00:00 24989 vappr        
  6. 18/01/2008 20:00:00 24990 vappr        
  7. 18/01/2008 20:00:00 24989 vappr        
  8. 18/01/2008 20:00:00        5.31    5.48    5.33
  9. 18/01/2008 20:00:00 24989 vappr        
  10. 18/01/2008 20:00:00 24990 vappr        
  11. 18/01/2008 20:00:00 24989 vappr            
  12. 18/01/2008 20:00:30        3.31        1.48    7.33
  13.  
OUTPUT file should look like this:
Filename:prstat-Ls-20080118-2000.xls (in differnt location)
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
  2. 18/01/2008     20:00:00    5.31     5.48     5.3
  3. 18/01/2008     20:00:30     3.31    1.48     7.33
  4.  
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
Oct 14 '08 #1
Share this Question
Share on Google+
17 Replies
P: 65
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
Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. #use Spreadsheet::ParseExcel;
  3. use Spreadsheet::ParseExcel::Simple;
  4.  
  5. my $oExcel = new Spreadsheet::ParseExcel;
  6. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
  7. my @file=glob("$dir");
  8. foreach my $f (@file){
  9. print "$f\n";
  10. my $xls = Spreadsheet::ParseExcel::Simple->read($f);
  11.   foreach my $sheet ($xls->sheets) {
  12.      while ($sheet->has_data) {  
  13.          my @data = $sheet->next_row;
  14.          print "@data[0]\n";
  15.      }
  16.   }
  17.  } 
  18.  
i have few doubts can anyone explain me the 2nd line from this :
1.first question:
Expand|Select|Wrap|Line Numbers
  1. C:\Performance_svap\misc>perl parse-excel.pl
  2. Scalar value @data[0] better written as $data[0] at parse-excel.pl line 16
  3. C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
  4.  
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
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY    HH:MM:SS     LAVG_1min    LAVG_5min    LAVG_15min
  2.  
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
Oct 17 '08 #2
nithinpes
Expert 100+
P: 410
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
Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. #use Spreadsheet::ParseExcel;
  3. use Spreadsheet::ParseExcel::Simple;
  4.  
  5. my $oExcel = new Spreadsheet::ParseExcel;
  6. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
  7. my @file=glob("$dir");
  8. foreach my $f (@file){
  9. print "$f\n";
  10. my $xls = Spreadsheet::ParseExcel::Simple->read($f);
  11.   foreach my $sheet ($xls->sheets) {
  12.      while ($sheet->has_data) {  
  13.          my @data = $sheet->next_row;
  14.          print "@data[0]\n";
  15.      }
  16.   }
  17.  } 
  18.  
i have few doubts can anyone explain me the 2nd line from this :
1.first question:
Expand|Select|Wrap|Line Numbers
  1. C:\Performance_svap\misc>perl parse-excel.pl
  2. Scalar value @data[0] better written as $data[0] at parse-excel.pl line 16
  3. C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
  4.  
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
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY    HH:MM:SS     LAVG_1min    LAVG_5min    LAVG_15min
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. while ($sheet->has_data) {   
  2.      my @data = $sheet->next_row; 
  3.     print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found 
  4.      } 
  5.  
Oct 17 '08 #3
P: 65
ok.. now what i did is to search for the required header & put them under one array.
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w
  2. use strict;
  3. use Spreadsheet::ParseExcel::Simple;
  4. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
  5. my @file=glob("$dir");
  6. my @output;
  7. foreach my $f (@file){
  8. print "$f\n";
  9.         print "Processing $f please wait\n";
  10.         my $xls = Spreadsheet::ParseExcel::Simple->read($f);
  11.   foreach my $sheet ($xls->sheets) {
  12.      while ($sheet->has_data) {  
  13.          my @data = $sheet->next_row;
  14.          my $data = 'DD/MM/YYYY';
  15.             if (grep {$_ eq $data} @data) {
  16.             print "Element $data found!\n" ;
  17.             # Add one element at the end of the array
  18.             push(@output, $data);
  19.         }
  20.             my $hr = 'HH:MM:SS';
  21.         if (grep {$_ eq $hr} @data) {
  22.             print "Element $hr found!\n" ;
  23.             push(@output, $hr);
  24.         }
  25.             my $la1 = 'LAVG_1min' ;
  26.         if (grep {$_ eq  $la1} @data) {
  27.             print "Element $la1 found!\n" ;
  28.             push(@output, $la1);
  29.         }
  30.         my $la5 = 'LAVG_5min' ;
  31.         if (grep {$_ eq  $la5} @data) {
  32.             print "Element $la5 found!\n" ;
  33.             push(@output, $la5);
  34.         }
  35.         my $la15 = 'LAVG_15min' ;
  36.         if (grep {$_ eq  $la15} @data) {
  37.             print "Element $la15 found!\n" ;
  38.             push(@output, $la15);
  39.         }
  40.      }
  41.      print "@output\n";
  42.   }
  43.  } 
  44.  
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 :
Expand|Select|Wrap|Line Numbers
  1.     my $la1 = 'LAVG_1min' ;
  2.         if (grep {$_ eq  $la1} @data) {
  3.             print "Element $la1 found!\n" ;
  4.             push(@output, $la1);
  5.         }
  6.         my $la5 = 'LAVG_5min' ;
  7.         if (grep {$_ eq  $la5} @data) {
  8.             print "Element $la5 found!\n" ;
  9.             push(@output, $la5);
  10.         }
  11.         my $la15 = 'LAVG_15min' ;
  12.         if (grep {$_ eq  $la15} @data) {
  13.             print "Element $la15 found!\n" ;
  14.             push(@output, $la15);
  15.         }
  16.  
any help plz ??
Regards,
Vijayarl
Oct 17 '08 #4
P: 65
hey nitinpes !!! thanks for your reply

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

Regards,
Vijayarl
Oct 17 '08 #5
P: 65
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:
Expand|Select|Wrap|Line Numbers
  1. 18/01/2008 18:36:00 133 root 4560K 816K sleep 59 0 0:00:00 0.0% picld/1
  2. 18/01/2008 18:36:00 58 root 13M 5784K sleep 59 0 0:00:15 0.0% vxconfigd/1
  3. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/53
  4. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/52
  5. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/51
  6. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/50
  7. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/49
  8. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/48
  9. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/47
  10. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:02 0.0% java/46
  11. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/45
  12. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/44
  13. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/43
  14. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/42
  15. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/41
  16. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/40
  17. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 6 17 0:00:00 0.0% java/38
  18. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/37
  19. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/36
  20. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:02 0.0% java/35
  21. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:07 0.0% java/34
  22. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:06 0.0% java/33
  23. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:07 0.0% java/32
  24. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/31
  25. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 57 0 0:00:02 0.0% java/30
  26. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/29
  27. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:01 0.0% java/28
  28. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/27
  29. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/26
  30. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/25
  31. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:17:03 0.0% java/22
  32. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:44 0.0% java/20
  33. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:06:47 0.0% java/19
  34. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:15:44 0.0% java/18
  35. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:06:39 0.0% java/17
  36. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:08:55 0.0% java/16
  37. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:57 0.0% java/15
  38. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:08:35 0.0% java/14
  39. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:54 0.0% java/13
  40. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:12:22 0.0% java/12
  41. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/11
  42. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/10
  43. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:00 0.0% java/9
  44. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:10 0.0% java/8
  45. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 28 10 0:00:18 0.0% java/7
  46. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/6
  47. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/5
  48. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 51 2 0:00:01 0.0% java/4
  49. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 59 0 0:00:00 0.0% java/3
  50. 18/01/2008 18:36:00 24992 vappr 1217M 1044M sleep 29 10 0:00:53 0.0% java/1
  51. 18/01/2008 18:36:00 627 daemon 2816K 856K sleep 59 0 0:00:00 0.0% rpcbind/1
  52. 18/01/2008 18:36:00           1.5 1.08 0.79
  53. 18/01/2008 18:36:30 24992 vappr 1217M 1044M sleep 21 10 0:15:37 1.7% java/23
  54. 18/01/2008 18:36:00           1.5 1.08 0.79
  55.  
i just want this line to be extracted from the above ouput :
Expand|Select|Wrap|Line Numbers
  1. 18/01/2008 18:36:00           1.5 1.08 0.79
  2.  
Regards,
Vijayarl
Oct 17 '08 #6
P: 65
nitinpes,

i got the problem !!! there was slight error in this:
Expand|Select|Wrap|Line Numbers
  1.  print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found  
  2.  
it has to be :
Expand|Select|Wrap|Line Numbers
  1. print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found 
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. DD/MM/YYYY  HH:MM:SS  LAVG_1min  LAVG_5min  LAVG_15min
  2. 18/01/2008       18:00:00      0.12             0.46              0.52
  3. 18/01/2008       18:00:30      0.09             0.21              0.79
  4. 18/01/2008       18:01:00      0.69             0.32              0.66
  5.  
Regards,
Vijayarl
Oct 17 '08 #7
nithinpes
Expert 100+
P: 410
That should have given you an idea of printing only the required elements. I had printed entire array. Replace:
Expand|Select|Wrap|Line Numbers
  1. print "@data\n" if(exists $data[4]);
  2.  
with:

Expand|Select|Wrap|Line Numbers
  1. print "$data[0]\t$data[1]\t$data[4]\t$data[5]\t$data[6]\n" if(exists $data[4]);
  2.  
Also, remember that index values are based on the sample format that you provided. If the file content is different, use index accordingly.
Oct 17 '08 #8
nithinpes
Expert 100+
P: 410
nitinpes,

i got the problem !!! there was slight error in this:
Expand|Select|Wrap|Line Numbers
  1.  print "@data\n" if(exists $data[4]); ##prints data if LAVG_1min value is found  
  2.  
it has to be :
Expand|Select|Wrap|Line Numbers
  1. print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found 
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. print "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"
  2.  
Oct 17 '08 #9
P: 65
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
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w
  2. use strict;
  3. use Spreadsheet::ParseExcel::Simple;
  4. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
  5. my @file=glob("$dir");
  6. my @output;my @outfile;
  7. foreach my $f (@file){
  8. print "$f\n";
  9.         print "Processing $f please wait\n";
  10.         my $xls = Spreadsheet::ParseExcel::Simple->read($f);
  11.   foreach my $sheet ($xls->sheets) {
  12.      while ($sheet->has_data) {  
  13.          my @data = $sheet->next_row;
  14.          my $data = 'DD/MM/YYYY';
  15.             if (grep {$_ eq $data} @data) {
  16.             print "Element $data found!\n" ;
  17.             # Add one element at the end of the array
  18.             push(@output, $data);
  19.         }
  20.             my $hr = 'HH:MM:SS';
  21.         if (grep {$_ eq $hr} @data) {
  22.             print "Element $hr found!\n" ;
  23.             push(@output, $hr);
  24.         }
  25.             my $la1 = 'LAVG_1min' ;
  26.         if (grep {$_ eq  $la1} @data) {
  27.             print "Element $la1 found!\n" ;
  28.             push(@output, $la1);
  29.         }
  30.         my $la5 = 'LAVG_5min' ;
  31.         if (grep {$_ eq  $la5} @data) {
  32.             print "Element $la5 found!\n" ;
  33.             push(@output, $la5);
  34.         }
  35.         my $la15 = 'LAVG_15min' ;
  36.         if (grep {$_ eq  $la15} @data) {
  37.             print "Element $la15 found!\n" ;
  38.             push(@output, $la15);
  39.         }
  40.         while ($sheet->has_data) {    
  41.      my @data = $sheet->next_row;
  42.    #print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found 
  43.         foreach my $m (@data){
  44.             if(exists $data[12]){
  45.                push(@outfile,$data[0]);
  46.                push(@outfile,$data[1]);
  47.                push(@outfile,$data[12]);
  48.                push(@outfile,$data[13]);
  49.                push(@outfile,$data[14]);
  50.                }
  51.             }   
  52.      }
  53.      }
  54.      print "@output\n";
  55.      print "@outfile\n";
  56.   }
  57.  } 
  58.  
now i want to know how to traverse rowwise & fetch the required data ???

plz help me out

Regards,
Vijayarl
Oct 17 '08 #10
P: 65
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.

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w
  2. use strict;
  3. use Spreadsheet::ParseExcel::Simple;
  4. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls';
  5. my @file=glob("$dir");
  6. my @output;my @outfile;
  7. foreach my $f (@file){
  8. print "$f\n";
  9.         print "Processing $f please wait\n";
  10.         my $xls = Spreadsheet::ParseExcel::Simple->read($f);
  11.   foreach my $sheet ($xls->sheets) {
  12.      while ($sheet->has_data) {  
  13.          my @data = $sheet->next_row;
  14.          my $data = 'DD/MM/YYYY';
  15.             if (grep {$_ eq $data} @data) {
  16.             print "Element $data found!\n" ;
  17.             # Add one element at the end of the array
  18.             push(@output, $data);
  19.         }
  20.             my $hr = 'HH:MM:SS';
  21.         if (grep {$_ eq $hr} @data) {
  22.             print "Element $hr found!\n" ;
  23.             push(@output, $hr);
  24.         }
  25.             my $la1 = 'LAVG_1min' ;
  26.         if (grep {$_ eq  $la1} @data) {
  27.             print "Element $la1 found!\n" ;
  28.             push(@output, $la1);
  29.         }
  30.         my $la5 = 'LAVG_5min' ;
  31.         if (grep {$_ eq  $la5} @data) {
  32.             print "Element $la5 found!\n" ;
  33.             push(@output, $la5);
  34.         }
  35.         my $la15 = 'LAVG_15min' ;
  36.         if (grep {$_ eq  $la15} @data) {
  37.             print "Element $la15 found!\n" ;
  38.             push(@output, $la15);
  39.         }
  40.         print "@output\n";
  41.         while ($sheet->has_data) {    
  42.      my @data = $sheet->next_row;
  43.    #print "$data[12]\n" if(exists $data[12]); ##prints data if LAVG_1min value is found 
  44.         #foreach my $m (@data){
  45.             if(exists $data[12]){
  46.                push(@outfile,$data[0]);
  47.                push(@outfile,$data[1]);
  48.                push(@outfile,$data[12]);
  49.                push(@outfile,$data[13]);
  50.                push(@outfile,$data[14]);
  51.                print "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";
  52.                }
  53.             #}   
  54.      }
  55.      }
  56.  
  57.      #print "@outfile\n";
  58.   }
  59.  } 
  60.  
output i got from this:
Expand|Select|Wrap|Line Numbers
  1. C:\Performance_svap\misc>perl excel.pl
  2. C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls
  3. Processing C:\Performance_svap\INPUT_FILES\prstat-Ls-20080118-1800.xls please wa
  4. it
  5. Element DD/MM/YYYY found!
  6. Element HH:MM:SS found!
  7. Element LAVG_1min found!
  8. Element LAVG_5min found!
  9. Element LAVG_15min found!
  10. DD/MM/YYYY HH:MM:SS LAVG_1min LAVG_5min LAVG_15min
  11. 18/01/2008      18:00:00        0.12    0.46    0.81
  12. 18/01/2008      18:00:30        0.16    0.44    0.79
  13. 18/01/2008      18:01:00        0.14    0.41    0.77
  14. 18/01/2008      18:01:30        0.13    0.38    0.75
  15. 18/01/2008      18:02:00        0.12    0.35    0.73
  16. 18/01/2008      18:02:30        0.11    0.33    0.71
  17. 18/01/2008      18:03:00        0.12    0.31    0.69
  18. 18/01/2008      18:03:30        0.11    0.29    0.67
  19. 18/01/2008      18:04:00        0.11    0.27    0.65
  20. 18/01/2008      18:04:30        0.11    0.26    0.63
  21. 18/01/2008      18:05:00        0.13    0.25    0.62
  22. 18/01/2008      18:05:30        0.22    0.26    0.61
  23. 18/01/2008      18:06:00        0.24    0.26    0.6
  24. 18/01/2008      18:06:30        0.21    0.25    0.58
  25. 18/01/2008      18:07:00        0.18    0.24    0.57
  26. 18/01/2008      18:07:30        0.14    0.23    0.55
  27. 18/01/2008      18:08:00        0.13    0.22    0.54
  28. 18/01/2008      18:08:30        0.12    0.21    0.52
  29. 18/01/2008      18:09:00        0.12    0.2     0.51
  30. 18/01/2008      18:09:30        0.12    0.19    0.5
  31. 18/01/2008      18:10:00        0.13    0.18    0.48
  32. 18/01/2008      18:10:30        0.24    0.21    0.48
  33. 18/01/2008      18:11:00        0.26    0.21    0.48
  34. 18/01/2008      18:11:30        0.23    0.21    0.47
  35. 18/01/2008      18:12:00        0.18    0.2     0.46
  36. 18/01/2008      18:12:30        0.15    0.2     0.45
  37. 18/01/2008      18:13:00        0.2     0.2     0.44
  38. 18/01/2008      18:13:30        0.36    0.23    0.44
  39. 18/01/2008      18:14:00        0.45    0.27    0.45
  40. 18/01/2008      18:14:30        1.58    0.54    0.54
  41. 18/01/2008      18:15:00        1.98    0.75    0.61
  42. 18/01/2008      18:15:30        1.38    0.72    0.6
  43. 18/01/2008      18:16:00        1.04    0.71    0.6
  44. 18/01/2008      18:16:30        0.82    0.68    0.6
  45. 18/01/2008      18:17:00        0.54    0.63    0.58
  46. 18/01/2008      18:17:30        0.39    0.59    0.57
  47. 18/01/2008      18:18:00        0.31    0.55    0.55
  48. 18/01/2008      18:18:30        0.29    0.52    0.54
  49. 18/01/2008      18:19:00        0.69    0.59    0.57
  50. 18/01/2008      18:19:30        0.95    0.67    0.59
  51. 18/01/2008      18:20:00        1.16    0.74    0.62
  52. 18/01/2008      18:20:30        1.09    0.77    0.63
  53. 18/01/2008      18:21:00        1.04    0.79    0.64
  54. 18/01/2008      18:21:30        0.77    0.75    0.64
  55. 18/01/2008      18:22:00        0.74    0.74    0.64
  56. 18/01/2008      18:22:30        0.64    0.71    0.63
  57. 18/01/2008      18:23:00        0.43    0.66    0.62
  58. 18/01/2008      18:23:30        0.3     0.61    0.6
  59. 18/01/2008      18:24:00        0.23    0.56    0.58
  60. 18/01/2008      18:24:30        0.2     0.52    0.57
  61. 18/01/2008      18:25:00        0.19    0.49    0.55
  62. 18/01/2008      18:25:30        0.21    0.46    0.55
  63. 18/01/2008      18:26:00        0.21    0.44    0.54
  64. 18/01/2008      18:26:30        0.21    0.42    0.52
  65. 18/01/2008      18:27:00        0.34    0.43    0.52
  66. 18/01/2008      18:27:30        0.36    0.42    0.52
  67. 18/01/2008      18:28:00        0.32    0.41    0.51
  68. 18/01/2008      18:28:30        0.47    0.43    0.52
  69. 18/01/2008      18:29:00        0.68    0.49    0.53
  70. 18/01/2008      18:29:30        0.93    0.57    0.55
  71. 18/01/2008      18:30:00        0.87    0.59    0.56
  72. 18/01/2008      18:30:30        0.99    0.64    0.58
  73. 18/01/2008      18:31:00        0.91    0.66    0.59
  74. 18/01/2008      18:31:30        0.92    0.68    0.6
  75. 18/01/2008      18:32:00        0.84    0.68    0.61
  76. 18/01/2008      18:32:30        1.07    0.75    0.63
  77. 18/01/2008      18:33:00        1.15    0.8     0.65
  78. 18/01/2008      18:33:30        1.04    0.81    0.66
  79. 18/01/2008      18:34:00        1.29    0.89    0.69
  80. 18/01/2008      18:34:30        1.47    0.97    0.72
  81. 18/01/2008      18:35:00        1.12    0.93    0.72
  82. 18/01/2008      18:35:30        1.33    1       0.75
  83. 18/01/2008      18:36:00        1.5     1.08    0.79
  84. 18/01/2008      18:36:30        1.53    1.12    0.81
  85. 18/01/2008      18:37:00        1.44    1.14    0.82
  86. 18/01/2008      18:37:30        1.54    1.2     0.86
  87. 18/01/2008      18:38:00        1.77    1.28    0.89
  88. 18/01/2008      18:38:30        1.71    1.32    0.92
  89. 18/01/2008      18:39:00        1.61    1.32    0.93
  90. 18/01/2008      18:39:30        1.86    1.41    0.98
  91. 18/01/2008      18:40:00        1.68    1.41    0.99
  92. 18/01/2008      18:40:30        1.81    1.46    1.02
  93. 18/01/2008      18:41:00        2.05    1.55    1.07
  94. 18/01/2008      18:41:30        1.99    1.59    1.09
  95. 18/01/2008      18:42:00        2.14    1.66    1.14
  96. 18/01/2008      18:42:30        2.08    1.69    1.16
  97. 18/01/2008      18:43:00        2.15    1.75    1.2
  98. 18/01/2008      18:43:30        2.09    1.77    1.23
  99. 18/01/2008      18:44:00        2.11    1.8     1.25
  100. 18/01/2008      18:44:30        2.1     1.83    1.28
  101. 18/01/2008      18:45:00        2.18    1.88    1.32
  102. 18/01/2008      18:45:30        2.23    1.92    1.35
  103. 18/01/2008      18:46:00        2.14    1.93    1.37
  104. 18/01/2008      18:46:30        2.2     1.96    1.4
  105. 18/01/2008      18:47:00        2.23    1.99    1.43
  106. 18/01/2008      18:47:30        2.42    2.06    1.47
  107. 18/01/2008      18:48:00        2.62    2.14    1.52
  108. 18/01/2008      18:48:30        2.67    2.2     1.56
  109. 18/01/2008      18:49:00        2.58    2.23    1.59
  110. 18/01/2008      18:49:30        2.46    2.23    1.61
  111. 18/01/2008      18:50:00        2.44    2.25    1.64
  112. 18/01/2008      18:50:30        2.35    2.25    1.65
  113. 18/01/2008      18:51:00        2.3     2.24    1.67
  114. 18/01/2008      18:51:30        2.28    2.25    1.69
  115. 18/01/2008      18:52:00        2.57    2.31    1.73
  116. 18/01/2008      18:52:30        2.69    2.37    1.77
  117. 18/01/2008      18:53:00        2.75    2.41    1.8
  118. 18/01/2008      18:53:30        2.83    2.47    1.84
  119. 18/01/2008      18:54:00        2.77    2.48    1.87
  120. 18/01/2008      18:54:30        3       2.57    1.92
  121. 18/01/2008      18:55:00        2.97    2.6     1.95
  122. 18/01/2008      18:55:30        3.08    2.66    1.99
  123. 18/01/2008      18:56:00        3.05    2.7     2.02
  124. 18/01/2008      18:56:30        2.98    2.71    2.05
  125. 18/01/2008      18:57:00        3.12    2.77    2.09
  126. 18/01/2008      18:57:30        2.86    2.74    2.11
  127. 18/01/2008      18:58:00        2.79    2.74    2.12
  128. 18/01/2008      18:58:30        2.75    2.73    2.14
  129. 18/01/2008      18:59:00        3.09    2.81    2.19
  130. 18/01/2008      18:59:30        3.21    2.88    2.23
  131.  
Regards,
Vijayarl
Oct 17 '08 #11
nithinpes
Expert 100+
P: 410
For writing to excel file, you may make use of Spreadsheet::WriteExcel module.
-Nithin
Oct 17 '08 #12
nithinpes
Expert 100+
P: 410
If you want to print only the contents of the array to new file you may simply open an excel sheet for writing :
Expand|Select|Wrap|Line Numbers
  1. open(EX,">my_result.xls") or die "error:$!"
  2.  
and print the required data to the file:
Expand|Select|Wrap|Line Numbers
  1. print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"; 
  2.  
Oct 17 '08 #13
P: 65
Hi nitinpes,

i get this error when i try to run :
Expand|Select|Wrap|Line Numbers
  1. C:\Performance_svap\misc>perl excel.pl
  2. String found where operator expected at excel.pl line 55, near "EX "$data[0]\t$d
  3. ata[1]\t$data[12]\t$data[13]\t$data[14]\n""
  4.         (Do you need to predeclare EX?)
  5. syntax error at excel.pl line 55, near "print"
  6. Execution of excel.pl aborted due to compilation errors.
  7.  
line 54 is about writing into a another excel file:
Expand|Select|Wrap|Line Numbers
  1. open(EX,'>C:\Performance_svap\INPUT_FILES\my_result.xls') or die "error:$!"
  2. print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"; #### line 54###
  3.  
Regards,
Vijayarl
Oct 17 '08 #14
P: 65
Ahhh !!! sorry there was no semicolon at the end of open stmt....silly mistake..

anyway's now this error, what it actually means:
Expand|Select|Wrap|Line Numbers
  1.  
  2. use of uninitialized value in concatenation (.) or string at excel.pl line 56.
  3.  
  4.  
Regards,
Vijayarl
Oct 17 '08 #15
nithinpes
Expert 100+
P: 410
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..
Oct 18 '08 #16
P: 65
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 ???

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w 
  2. use strict; 
  3. use Spreadsheet::ParseExcel::Simple; 
  4. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls'; 
  5. my @file=glob("$dir"); 
  6. my @output;
  7. my $loadavg;
  8. #my @outfile;
  9. my $str;
  10. my $str1;
  11. my @data;
  12. my $date;
  13. foreach my $f (@file){
  14.         $str = substr($f,32,39);
  15.         $str1 = substr($str,0,9);
  16.         $loadavg ="c:\\Performance_svap\\OUTPUT_FILES\\$str";
  17.         print "$loadavg\n";
  18.          if($str1 =~/prstat-Ls/){
  19.         print "$f\n"; 
  20.         print "Processing $f please wait\n"; 
  21.         my $xls = Spreadsheet::ParseExcel::Simple->read($f); 
  22.         foreach my $sheet($xls->sheets) { 
  23.             while ($sheet->has_data) {   
  24.             @data = $sheet->next_row; 
  25.             my $date = 'DD/MM/YYYY'; 
  26.         if (grep {$_ eq $date} @data) { 
  27.             push(@output, $date); 
  28.         } 
  29.             my $hr = 'HH:MM:SS'; 
  30.         if (grep {$_ eq $hr} @data) { 
  31.             push(@output, $hr); 
  32.         } 
  33.             my $la1 = 'LAVG_1min' ; 
  34.         if (grep {$_ eq  $la1} @data) { 
  35.             push(@output, $la1); 
  36.         } 
  37.         my $la5 = 'LAVG_5min' ; 
  38.         if (grep {$_ eq  $la5} @data) { 
  39.             push(@output, $la5); 
  40.         } 
  41.         my $la15 = 'LAVG_15min' ; 
  42.         if (grep {$_ eq  $la15} @data) { 
  43.             push(@output, $la15); 
  44.         }    
  45.         while ($sheet->has_data) {     
  46.         my @data = $sheet->next_row; 
  47.             if(exists $data[12]){ 
  48.                 open(EX,">$loadavg") or die "Can't open open $loadavg:$!";
  49.                 print EX "$output[0]\t$output[1]\t$output[2]\t$output[3]\t$output[4]\n";                      
  50.                 print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n"; 
  51.                 close EX;        
  52.                 }
  53.             }
  54.         }
  55.         }
  56.     }  
  57. }  
  58.  
thanks & regards,
Vijayarl
Oct 20 '08 #17
P: 65
hi All,

i got the required result.

Thanks to nitinpes !!! i was almost given up... he came & gave me the right inputs..

Working Script:
Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl -w 
  2. use strict; 
  3. use Spreadsheet::ParseExcel::Simple; 
  4. my $dir = 'C:\Performance_svap\INPUT_FILES\*.xls'; 
  5. my @file=glob("$dir"); 
  6. my @output;
  7. my $loadavg;
  8. my $str;
  9. my $str1;
  10. my @data;
  11. my $date;
  12. foreach my $f (@file){
  13.         $str = substr($f,32,39);
  14.         $str1 = substr($str,0,9);
  15.         $loadavg ="c:\\Performance_svap\\OUTPUT_FILES\\$str";
  16.          if($str1 =~/prstat-Ls/){
  17.         print "$f\n"; 
  18.         print "Processing $f please wait\n"; 
  19.         my $xls = Spreadsheet::ParseExcel::Simple->read($f); 
  20.         foreach my $sheet($xls->sheets) { 
  21.             while ($sheet->has_data) {   
  22.             @data = $sheet->next_row; 
  23.             my $date = 'DD/MM/YYYY'; 
  24.         if (grep {$_ eq $date} @data) { 
  25.             push(@output, $date); 
  26.         } 
  27.             my $hr = 'HH:MM:SS'; 
  28.         if (grep {$_ eq $hr} @data) { 
  29.             push(@output, $hr); 
  30.         } 
  31.             my $la1 = 'LAVG_1min' ; 
  32.         if (grep {$_ eq  $la1} @data) { 
  33.             push(@output, $la1); 
  34.         } 
  35.         my $la5 = 'LAVG_5min' ; 
  36.         if (grep {$_ eq  $la5} @data) { 
  37.             push(@output, $la5); 
  38.         } 
  39.         my $la15 = 'LAVG_15min' ; 
  40.         if (grep {$_ eq  $la15} @data) { 
  41.             push(@output, $la15); 
  42.         }
  43.         open(EX,">$loadavg") or die "Can't open open $loadavg:$!";
  44.         print EX "$output[0]\t$output[1]\t$output[2]\t$output[3]\t$output[4]\n"; 
  45.         while ($sheet->has_data) {     
  46.         my @data = $sheet->next_row;
  47.             if(exists $data[12]){ 
  48.                 print EX "$data[0]\t$data[1]\t$data[12]\t$data[13]\t$data[14]\n";              
  49.                 }
  50.             }
  51.         }
  52.         close EX;
  53.         }
  54.     }  
  55. }  
  56.  
  57.  
Thanks to all...

Regards,
Vijayarl
Oct 20 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Question stats

  • viewed: 5815
  • replies: 17
  • date asked: Oct 14 '08
BYTES.COM © 2019 About Bytes | Advertise on Bytes | Privacy Policy | Manage Cookies
Sitemap | Perl Answers Sitemap | Perl Insights Sitemap

Follow us to get the Latest Bytes Updates