Solved

.XLS to CSV using Perl

Posted on 2007-03-25
6
564 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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tenRun challenge 28 100
SQL400 max size 5 93
word0 challenge 3 91
Re-position sub-options beneath the TAB 7 88
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…
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.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

777 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