Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Excel to Perl

by sabersd (Initiate)
on Mar 20, 2002 at 18:36 UTC ( #153068=perlquestion: print w/ replies, xml ) Need Help??
sabersd has asked for the wisdom of the Perl Monks concerning the following question:

I have about 75 excel workbooks and about 5 to 10 more coming in each week. I need to convert these files to a tab delimited format, so they can be picked up by another perl program that updates or inserts records into a database. The workbooks all have at least 2 worksheets and up to 5. The first worksheet is to be ignored. I have been looking at the Win32::OLE module, but can't really figure it out yet. I am learning perl on the go. I would like to be able to point to a directory and run all .xls files found there. I would also like only one tab delimited file to be written per execusion. Not sure if this is possible. Any suggestions would be greatly appreciated.

Comment on Excel to Perl
Re: Excel to Perl
by broquaint (Abbot) on Mar 20, 2002 at 18:40 UTC
Use the right tool (was Re: Excel to Perl)
by dragonchild (Archbishop) on Mar 20, 2002 at 18:42 UTC
    Don't use Perl for this. Use VBA. It's very easy to do this conversion to tab-delim files.

    Now, if you were going to fold the reading of the files into the Perl program, so that it could read either tab-delim or .xls files, then use Spreadsheet::ParseExcel.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

      I'm not knowledgable on vba, where could I find out more info on this? Would I be able to do this in the excel visual basic editor? Thanks
        Yes, it's done in Excel's VB Editor. Read the helpfiles (i.e., hit F1). That's all I did.

        ------
        We are the carpenters and bricklayers of the Information Age.

        Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Re: Excel to Perl
by talexb (Canon) on Mar 20, 2002 at 19:01 UTC
    Be warned that depending on the size of the Excel file, Perl may run out of memory, as I reported here.

    --t. alex

    "Here's the chocolates, and here's the flowers. Now how 'bout it, widder hen, will ya marry me?" --Foghorn Leghorn

Re: Excel to Perl
by cacharbe (Curate) on Mar 20, 2002 at 19:23 UTC
    Alright, I'll finish the Tutorial and get it up by the week-end.

    Read my Scratch Pad for a complete discussion on getting data from and manipulating data in an excel worksheet using Win32::OLE.

    Honestly, if you're going to be opening the workbooks anyway, there is no reason to use two scripts to accomplish this task, just iterate through the Sheets object of each workbook starting from the 1 position. Otherwise use the SaveAs function of the Worksheet object

    use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # Die on Errors. ####################################### ## ::Warn = 2 throws the errors, but ## ## expects that the programmer deals ## ####################################### my $excelfile = 'c:\temp\win32\tabexample.xls'; my $excelout = 'c:\temp\win32\tabexample.tab'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts}=0; my $Book = $Excel->Workbooks->Open($excelfile); $Book->SaveAs({Filename =>$excelout, FileFormat => xlCurrentPlatformText});
    C-.

    Update: I thought I should make a slight clarification about iterating through the Worksheets set based on sheet count. I was trying to say that the Worksheets object is 1 based, not 0 based. If, indeed, you wanted to skip the first work sheet, you would start with 2.

    my $sheetcnt = $Book->Worksheets->Count(); foreach (2..$sheetcnt){ print $Book->Worksheets($_)->{Name} ."\n"; my $sheet = $Book->Worksheets($_); ##Do something with the sheet object## }

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://153068]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2014-12-29 02:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (184 votes), past polls