Solved

.XLS to CSV using Perl

Posted on 2007-03-25
6
578 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

696 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