Just to predicate this post, I am a fairly inexperienced programmer and totally new to Perl. I am currently writing my first script to become more familiar with this language. I use Windows Vista and have Strawberry Perl on my computer.

Right now I'm working on a script that reads through a directory of Excel files of student records, sorts them by name and version number, and picks out only the most recent version. File names might look something like:

(the format is in last-name_first-name_major_version#)

doe_john_ee_v02.xlsx, doe_john_ee_v05.xlsx, smith_mary_cpe_v02.xlsx

The directory has over 1000+ files in it but filters out everything except the most recent ones(about 250 files). The script cycles through the remaining files, opens them, picks out a date from one cell, and then prints that date, the student name, and student major to an output.

Currently my script works fine but is running extremely slowly. It takes 5-7 minutes to open and close those 250 files which seems excessively long. I'm hoping that someone might be able to provide a little insight into this problem and provide a method to improve performance.

My script utilizes the OLE interface to work with Excel. At the beginning of my code I have:

use OLE;
use Win32::OLE;
  my($xapp) = CreateObject OLE 'Excel.Application' || die $!;
    $xapp->{'Visible'} = 0;

[download]

At this point I sort through the files and pick out the relevant ones. I then cycle through them (@dateSort) to open the files which can be seen from my code below:

for $byDate(@dateSort) {
    @lines=split/_/,$byDate;

#
#Read file names in order to retrieve latest revision
#date from Excel sheets
#

    $name=join "_", @lines[1,2,3,4];

    #
    # Open the excel file
    #
        $fileName = $current_dir.'\\'.$name;
        if ($output ne '') {
            print STDOUT "Opening $name";
        }
        $wkb = $xapp->Workbooks->Open($fileName);

        if ( ! $wkb ) { 
                 print STDOUT "\n\nCan't open $name. File name format 
+incompattible.\n"; exit;  
        }

        #Open correct sheet from wkbk

    my $wks = $wkb->Worksheets(1);
     
    #Find the latest revision date from the sheet

    $lastUpdate = $wks->Cells(8,B)->{'Value'};

        $wkb->{Saved} = 1;        
        $wkb->Close();
             $xapp->Quit();

[download]

And thats it. Does anyone know why it's taking so long? Is there anything I can do to speed this process up. Any help would be much appreciated. Thanks, John

In reply to Speeding up my script by applefiend

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.