By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,221 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,221 IT Pros & Developers. It's quick & easy.

how to extract data from excel that satifies a match condition

P: 3
I will be obliged if anybody can help me with this problem:
I am trying to extract data from an excel sheet that matches IDs given in column 4 of the excel sheet.I have stored those query IDs in an array (@names). After I look for the match in this section of the code:
if ($value=~/^$names[$k]$/), I want to write out only those rows that satisfy the above natch condition. But currently the code I have here writes out everything. How do I get it to selectively write out data?

Expand|Select|Wrap|Line Numbers
  1. # Array for the IDs that I will be looking in the excel sheet
  2. @names = (1,3,4);
  3.  
  4. foreach my $ref (@names) {
  5.     chomp($ref);
  6.  
  7. }
  8. #===================================================
  9.  
  10. my $kk=@names;
  11. use strict;
  12. use Win32::OLE qw(in with);
  13. use Win32::OLE::Const 'Microsoft Excel';
  14. $Win32::OLE::Warn = 3; # die on errors...
  15.  
  16. # get already active Excel application or open new
  17. my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
  18. || Win32::OLE->new('Excel.Application', 'Quit'); # get already active Excel
  19. # application or open new
  20.  
  21. # open Excel file 
  22.  
  23. my $file = "C:\\Data\\Projects\\iavarone\\data_extraction\\Samples_table.xls";
  24. my $Book = $Excel->Workbooks->Open($file); 
  25. # select worksheet number 4 (you can also select a worksheet by name)
  26. my $Sheet = $Book->Worksheets(5);
  27. #defining the row and column to look for the IDs stored in @names array
  28. foreach my $row (2..540) 
  29.     {
  30.     foreach my $col (4)
  31.         {
  32. # skip empty cells
  33.     next unless defined $Sheet->Cells($row,$col)->{'Value'};
  34.               $value = $Sheet->Cells($row,$col)->{'Value'};
  35.  
  36. for ($k=0;$k<$kk;$k++) 
  37. {if ($value=~/^$names[$k]$/)
  38. {
  39. my $array = $Sheet->Range("A1:H540")->{'Value'};
  40. foreach my $cell_ref (@$array) {
  41. foreach my $value1 (@$cell_ref) {
  42. print "$value1\t";
  43.         }
  44. print "\n";
  45. }
  46.  
  47. }
  48. }
  49. }
  50. }
Apr 3 '08 #1
Share this Question
Share on Google+
1 Reply


nithinpes
Expert 100+
P: 410
Should ideally work if you change:
Expand|Select|Wrap|Line Numbers
  1. for ($k=0;$k<$kk;$k++) 
  2. {if ($value=~/^$names[$k]$/)
  3. {
  4. my $array = $Sheet->Range("A1:H540")->{'Value'};
  5. foreach my $cell_ref (@$array) {
  6. foreach my $value1 (@$cell_ref) {
  7. print "$value1\t";
  8.         }
  9. print "\n";
  10. }
  11. }
  12. }
  13.  
  14.  
to:

Expand|Select|Wrap|Line Numbers
  1. my $array = $Sheet->Range("A1:H540")->{'Value'};
  2. foreach my $cell_ref (@$array) {
  3. foreach my $value1 (@$cell_ref) {
  4. my $found=0;
  5. for ($k=0;$k<$kk;$k++) {
  6. if($value1=~/^$names[$k]$/) {print "$value1\t" ; $found=1;} 
  7. }
  8. print "\t" if($found==0); ###leave blank cells if not matched
  9.  }
  10. print "\n";
  11. }
  12.  
  13.  
Apr 4 '08 #2

Post your reply

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