473,388 Members | 876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

How to delete a line in excel file ???

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
17 6414
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
410 Expert 256MB
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
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
hey nitinpes !!! thanks for your reply

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

Regards,
Vijayarl
Oct 17 '08 #5
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
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
410 Expert 256MB
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
410 Expert 256MB
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
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
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
410 Expert 256MB
For writing to excel file, you may make use of Spreadsheet::WriteExcel module.
-Nithin
Oct 17 '08 #12
nithinpes
410 Expert 256MB
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
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
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
410 Expert 256MB
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
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
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

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

Similar topics

1
by: smonczka | last post by:
I have a DTS package that needs to refresh data in 3 separate Excel spreadsheets on a daily basis. The problem is that unless I manually delete the previous day's data, it appends rather than...
7
by: Smitty | last post by:
I have a function that imports an Excel file into an Access table using SQL. I then close the OleDataReader and the OleDbConnection, then dispose the OleDbCommand, then OleDbConnection. The calling...
2
by: Bob | last post by:
In the following code, when the DELETE line is executed I get a dialog asking if I want to delete or to cancel. How do I turn it off? Also when the first CLOSE line is executed, the application so...
8
by: DavidB | last post by:
I'm trying to delete a file with File.Delete("c:\Documents and Settings\%username%\Application Data\Microsoft\Excel\excel*.xlb") On doing this I get an Exception "Illegal characters in path" -...
1
by: Murali via DotNetMonster.com | last post by:
Hi, I am uploading the excel file to the server and reading the data and dumping in to sqlserver. Now i want to delete the excel file from the server, but i am getting a error like "file in...
3
by: Rene | last post by:
Hi Proggies, i try to delete an excel worksheet but nothing happens (work on vb.net 2005) ..... xlSheet = xlApp.Sheets("Master") xlSheet.select xlSheet.delete also try
1
by: SteveBark | last post by:
Hello all I am currently trying to develop a script that will take a value from an Excel spreadsheet cell and use that to run a query against an Access table to delete all rows that match the...
2
by: Bxitty | last post by:
Hello all, I am trying the below code to delete a worksheet using VB.NET..I amgeeting an error with the delete...any idea where this could be wrong.. Dim str_path As String Dim...
6
by: Annalyzer | last post by:
My database must produces a .csv file with a header line that is different from the detail lines. This .csv file contains a monthly report to an outside agency and is uploaded online so it must...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.