?
Solved

.XLS to CSV using Perl

Posted on 2007-03-25
6
Medium Priority
?
584 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The purpose of this article is to demonstrate how we can use conditional statements using Python.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

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