.XLS to CSV using Perl

Hello,
I need your urgent help!
I need to pass 3 excel files to a Perl program and need to generate a csv file containing a merged version. I am not really a Perl programmer, but this is the part of my job which I need to complete ASAP. Your help will be highly appreciated.
LVL 1
MPJDAsked:
Who is Participating?
 
nedfineCommented:
this is just a sample script. I have tried it for just 4 rows in each excel files.

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;                                

# get already active Excel application or open new
 $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  


$infile="final.txt";


# open Excel file

 $Book1 = $Excel->Workbooks->Open("d:/Nithin/br.xls");
 $Sheet1 = $Book1->Worksheets(1);
 $Book2 = $Excel->Workbooks->Open("d:/Nithin/brcity.xls");
 $Sheet2 = $Book2->Worksheets(1);
 $Book3 = $Excel->Workbooks->Open("d:/Nithin/brfl.xls");
 $Sheet3 = $Book3->Worksheets(1);
 $Book4 = $Excel->Workbooks->Open("d:/Nithin/out.xls");
 $Sheet4 = $Book4->Worksheets(1);

$row = 2;
$col = 1;
$w_row=2;
$w_col=1;

$data=0;

while($row<4)
{
 
 
     #$Sheet4->Cells($w_row,1)->{'Value'} = $Sheet1->Cells($row,1)->{'Value'};
    # $Sheet4->Cells($w_row,2)->{'Value'} = $Sheet1->Cells($row,2)->{'Value'};
    # $Sheet4->Cells($w_row,3)->{'Value'} = $Sheet1->Cells($row,3)->{'Value'};
     $br_no = $Sheet1->Cells($row,1)->{'Value'} ;
     $line_no = $Sheet1->Cells($row,2)->{'Value'} ;
     $Sheet4->Cells($w_row,1)->{'Value'} = $br_no;
     $Sheet4->Cells($w_row,2)->{'Value'} = $line_no;
     print "\n $br_no $line_no";
     
     while($row1<4)
     {            
         if($br_no == $Sheet2->Cells($row1,1)->{'Value'})
         {
           $Sheet4->Cells($w_row,4)->{'Value'} = $Sheet2->Cells($row1,3)->{'Value'};
           $Sheet4->Cells($w_row,5)->{'Value'} = $Sheet2->Cells($row1,4)->{'Value'};
           $Sheet4->Cells($w_row,6)->{'Value'} = $Sheet2->Cells($row1,5)->{'Value'};          
           last;
           
         }
         $row1++;
     }  
     
     $row1=2;
     while($row1<4)
          {            
              if($line_no == $Sheet2->Cells($row1,2)->{'Value'})
              {
                print "\n $w_row";
                $Sheet4->Cells($w_row,7)->{'Value'} = $Sheet3->Cells($row1,3)->{'Value'};
                $Sheet4->Cells($w_row,8)->{'Value'} = $Sheet3->Cells($row1,4)->{'Value'};                                
                last;
               
           }
           
           $row1++;
     }  
     
     
 
  $row++;
  $w_row++;
}
 

$Book4->Save;
$Book4->Close;
$Book1->Close;
$Book2->Close;
$Book3->Close;
0
 
TintinCommented:
Can the Excel files be saved in CSV format first?

If not, then either use Spreadsheet::ParseExcel module

http://search.cpan.org/dist/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel.pm

or if you are on Windows box, you could use Win32::OLE to achieve the same type of thing you could do with VB.
0
 
nedfineCommented:
can you show me a sample of your excel files. This  can be done using  Win32::OLE.
I have the code to read excel files. i can modify it according to your needs.

thank you
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MPJDAuthor Commented:
The Excel Files can be saved in CSV format.
The sample is:
1st file:

Branch_no
Line_no
BR_Manager

2nd File:

Branch_no
Line_no
BR_city
BR_state
BR_zip

3rd file:

Branch_no
Line_no
FName
LName

I want all the fields from all 3 files and the join columns will be Branch_no and Line_no
0
 
nedfineCommented:
can you please give some values for the fields so that i can test.
and how big are these excel files.. how many rows?
0
 
MPJDAuthor Commented:
Thanks nedfine,

This is certainly helping me!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.