Solved

.XLS to CSV using Perl

Posted on 2007-03-25
6
566 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:MPJD
  • 3
  • 2
6 Comments
 
LVL 48

Expert Comment

by:Tintin
ID: 18789378
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
 
LVL 8

Expert Comment

by:nedfine
ID: 18790630
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
 
LVL 1

Author Comment

by:MPJD
ID: 18793520
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
LVL 8

Expert Comment

by:nedfine
ID: 18797973
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
 
LVL 8

Accepted Solution

by:
nedfine earned 500 total points
ID: 18798005
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
 
LVL 1

Author Comment

by:MPJD
ID: 18800712
Thanks nedfine,

This is certainly helping me!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
word0 challenge 3 101
wordappend challenge 8 202
Control Number of Log Files -Perl 7 87
Perl Snippet to Parse String 1 23
Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question