Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Parsing multiple excel files in perl

by reaper9187 (Scribe)
on Oct 15, 2012 at 10:15 UTC ( #999054=perlquestion: print w/replies, xml ) Need Help??
reaper9187 has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone . i'm new to perl and this forum . pardon any mistakes.

Here goes my question: I'm currently working on an application that parses through multiple excel files (Spreadsheet::ParseExcel module ) and compares values (by rows) sequentially and gives out the result of the comparison. I'm not able to figure out as to how to manipulate or use the multiple file handles for excel sheets.

If someone could just mention the syntax, i'll be most grateful . Thanks in advance

Replies are listed 'Best First'.
Re: Parsing multiple excel files in perl
by marto (Archbishop) on Oct 15, 2012 at 10:19 UTC

    Since there are more than one way to read Excel files you should make it easy for us to help you and show us the code you're working with.

      Thanks for the reply

      This part of the application is a small portion of the entire software package i'm working on. Haven't written the code yet for this . Was looking for some kind of a headstart

      I have developed a parser for a single workbook for another section of the package but couldn't understand the process for multiple file handles.

        "I have developed a parser for a single workbook"

        So are not using a module such as Spreadsheet::ParseExcel or Win32::OLE and have hand rolled a solution? Again, if you don't bother to show us what you're doing and what you're having problems with you make it harder to help, and drawing information piecemeal gets boring after a while.

        Read and understand How do I post a question effectively?.

Re: Parsing multiple excel files in perl
by nemesdani (Friar) on Oct 15, 2012 at 10:24 UTC
    It depends on what your approach is. Win32::OLE? Spreadsheets::Excel? Excel::Writer? Else?

    Hard to provide any help without your code.

    I'm too lazy to be proud of being impatient.
      I'm using Spreadsheet::ParseExcel
Re: Parsing multiple excel files in perl
by grizzley (Chaplain) on Oct 15, 2012 at 12:12 UTC
    Seeing the codes you posted I guess your problem is really with design rather then with Perl itself. Codes posted are not complete, so I assume you face following:

    You have GUI application, in which after pressing some button 'Open file' dialog is displayed, where you choose file to be opened, then subroutine (which you pasted) is run in order to do stuff with this file. Am I correct that your problem is that you don't know how to allow user choosing more than one file from dialog box? And another thing is how to pass result of this dialog box to subroutine in order to open more files?

    This kind of information is what we need. If you answer yes to those questions, then you should consider following two approaches (I don't know API of GUI you use and don't know if it allows such operations): 'open file' dialog window should allow user to choose more than one file (standard way on Windows is with Ctrl key) or you should open 'open file' dialog window in the loop as many times as there are files to be processed.

    So if you were able to present following code of your GUI:

    $window->create("my main window"); $window->addbutton(somex, somey, somewidth, someheight, name, \&mysub) +; sub mysub { my $result = $window->openFileDialog(...); # error check open FH "$result" or die $!; # parsing file close FH; }
    you could get proper help from us. Of course in case my assumptions are correct.
      Hi.. Thank you for replying
      Well you pretty much right about the GUI part.
      However, my problem is slightly different from the one u mentioned here.
      I'm actually trying to choose multiple files using multiple dialog boxes( jus a part of the user interface) and then pass these filenames as arguments to the parser which then sequentially checks the cell values and compares it to a set threshold and gives out the results. I'm not able to parse multiple files simultaneously after passing the filenames as arguments
      Thanks again
        Then you have problem with algorithm parsing files? Because opening multiple files is simple, you just use different file handles:
        open FH, "file1" or die $!; open FH2, "file2" or die $!; $readlinefromfile1 = <FH>; $readlinefromfile2 = <FH2>; $readfromfile1again = <FH; close FH2; close FH;
Re: Parsing multiple excel files in perl
by t_rex_joe (Sexton) on Oct 15, 2012 at 16:31 UTC

    reaper, Here's a sample on howto take a workbook split into multiple sheets -> csv.

    ############################### my $ver = "1.0.0"; my $date = "2005-08-25"; my $stime = time(); use warnings; use strict; use Getopt::Long; use joes_pm; use Spreadsheet::ParseExcel; $|++; #workbook = $wb = whole file #worksheet = $ws = each tab $wb = Spreadsheet::ParseExcel::Workbook->Parse($if); @wss = (); @wss = @{$wb->{Worksheet}}; print "WSS: \"@wss\"\n"; my $file = $wb->{File}; print "FILE: \"$file\"\n"; my $wsct = $wb->{SheetCount}; print "WSCT: \"$wsct\"\n"; print "\n\n"; print "DUMP EACH SHEET\n"; $ws = undef; $wsname = undef; foreach $ws (@wss) { $wsname = undef; $minrow = 0; $maxrow = 0; $mincol = 0; $maxcol = 0; print "WS: \"$ws\"\n"; $wsname = $ws->{Name}; print "WSNAME: \"$wsname\"\n"; if($wsname =~ /sum/i) { print "FOUND SUM SHEET NEXT..\n\n"; next; } # $minrow = $ws->{MinRow}; # $maxrow = $ws->{MaxRow}; # $mincol = $ws->{MinCol}; # $maxcol = $ws->{MaxCol}; # ($minrow, $maxrow) = $ws->RowRange(); # ($mincol, $maxcol) = $ws->ColRange(); $minrow = 0; $maxrow = 65535; $maxrow = 1000; $mincol = 0; $maxcol = 255; $maxcol = 100; print "MINROW: \"$minrow\"\n"; print "MAXROW: \"$maxrow\"\n"; print "MINCOL: \"$mincol\"\n"; print "MAXCOL: \"$maxcol\"\n"; print OUT "--------------------------------------------------------- +- WSNAME: \"$wsname\".. START\n"; for($minrow = 0; $minrow <= $maxrow; $minrow++) { $rowval = undef; if($debug == 0) { if(!($minrow % 1000)) { print "."; } if(!($minrow % 5000)) { print "$minrow"; } if(!($minrow % 25000)) { print "\n"; } } if($debug == 1) { print "ROW: \"$minrow\"\n"; } for($mincol = 0; $mincol <= $maxcol; $mincol++) { if($debug == 1) { print "ROW: \"$minrow\"\n"; } if($debug == 1) { print "COL: \"$mincol\"\n"; } $cell = undef; $val = undef; $cell = $ws->{Cells}[$minrow][$mincol]; if($cell) { # $val = $cell->Value; #Formatted Value # if((!$val) || ($val eq "")) { $val = ""; } # if($debug == 1) { print "FORMAT VAL: \"$val\"\n"; } $val = $cell->{Val}; #Original Value if((!$val) || ($val eq "")) { $val = ""; } if($debug == 1) { print "ORIG VAL: \"$val\"\n"; } $val = clean($val); $val = lc($val); $val =~ s/\,//g; $rowval .= $val . ","; if($debug == 1) { sleep 1; } } #EO CELL else { if($debug == 1) { print "CELL IS NO DEFINED\n"; } $val = ""; $rowval .= $val . ","; } #EO !CELL if($debug == 1) { print "\n"; } if($debug == 1) { sleep 1; } } #EO COL #### CHECK TO SEE IF ROW IS BLANK if($debug == 1) { print "ROWVAL: \"$rowval\"\n"; } $val = undef; $val = $rowval; $val =~ s/\,//g; if($debug == 1) { print "VAL: \"$val\"\n"; } if($val eq "") { if($debug == 1) { print "ROWVAL IS BLANK... NEXT\n"; } next; } #### EO CHECK TO SEE IF ROW IS BLANK print OUT "$rowval\n"; if($debug == 1) { sleep 1; } } #EO ROW print OUT "--------------------------------------------------------- +- WSNAME: \"$wsname\".. DONE\n"; print "\n\n"; } #EO EACH WS print "\n\n"; ########################################## EO INPUT FILE


      Every time you create a post here, you get a reminder to use code tags, it literally looks like
      Use:  <p> text here (a paragraph) </p>
      and:  <code> code here </code>
      to format your post; it's "PerlMonks-approved HTML"

      so follow the advice, and put your code in between code tags, its where code (and data) goes :)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://999054]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2018-05-28 05:42 GMT
Find Nodes?
    Voting Booth?