Reading .xls to .csv in Perl on Linux - How to read only first worksheet

Linux

Hey all. I'm trying to read an .xls file into a .csv file and have the code to do it (forget where I copied it from) and although I remember seeing examples of reading a specific worksheet, I can't find any good examples.

I've attached the code to this thread.

What I want to do is simply read the first, and only the first, worksheet associated with an .xls file.

Any help would be appreciated.
#!/usr/bin/perl
# xls2csv.pl - parses an excel spreadsheet and writes it back out as a csv file (copied).
# receives 2 arguement - 
#   $ARGV[0] - Excel filename
#   $ARGV[1] - New Filename

use strict; 
# make sure all arguements were provided
if(!(defined $ARGV[0])) { 
    print "Usage: ", $0 , " Excel_File\n"; 
    exit; 
} 

if(!(defined $ARGV[1])) { 
    print "Usage: ", $1 , " Starting Line Number\n"; 
    exit; 
} 

#if(!(defined $ARGV[2])) { 
#    print "Usage: ", $2 , " Need to define input directory\n"; 
#    exit; 
#} 

# Package declarations
use Time::localtime;
use File::Copy;
use Spreadsheet::ParseExcel; 

# Initialize Objects #####################################################
my $oExcel = new Spreadsheet::ParseExcel; 
my $tm = localtime;
my $xcount;
my $ycount;
my $oWkS;
my $oWkC;
my $filename;

# datafile path $ARGV[2] should be either blank or ./, depending on how this script is ran
# If it is ran from within a program, send blank. If it is ran from command line in current directory, send ./
my $datapath=$ARGV[2];
#"./";

# Parse(Read) in the spreadsheet
my $oBook = $oExcel->Parse($datapath.$ARGV[0]); 
my $Rcv = 0;

# Split filename up
my @filesplit = split /\./,$ARGV[0];

# export the file into a csv file format
#my $Sheet = $Book->Worksheets(1);
foreach $oWkS (@{$oBook->{Worksheet}}) { 
   # check to see if the worksheet has any data, if yes then process
   if (defined $oWkS->{MaxRow}) 
   {
      $filename = $datapath . @filesplit[0] . ".csv";
      open(OUT, ">" . $filename) || die "Could not open ".$filename."\n";    
      
      for(my $xcount = $oWkS->{MinRow}; 
         defined $oWkS->{MaxRow} && $xcount <= $oWkS->{MaxRow}; $xcount++) { 
         if ( $xcount <= 7 ) {
             next;
         }
         #for(my $ycount = $oWkS->{MinCol} ; 
         for(my $ycount = 0 ; 
            defined $oWkS->{MaxCol} && $ycount <= $oWkS->{MaxCol} ; $ycount++) { 
                $oWkC = $oWkS->{Cells}[$xcount][$ycount]; 
                print OUT $oWkC->Value, if($oWkC); 
                print OUT "|", if($ycount < $oWkS->{MaxCol});
         }  # end columns for
         print OUT "\n";
      } #end rows for
      close(OUT);
   } # end if
} # end foreach worksheet

# make a copy of the file for loading
#my $loadfile=$datapath . $ARGV[0] . $ARGV[1] . ".csv";
#copy($filename, $loadfile);

Open in new window

WebspeederAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
Remove the "foreach $oWkS" loop and add "$oWkS = $oBook->{Worksheet}[0]" where the foreach is.

That should only print out the first worksheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WebspeederAuthor Commented:
I changed the for each block to the code below and now the CSV file is 0 bytes. Did I do this correctly?
#foreach $oWkS (@{$oBook->{Worksheet}}) { 
   # check to see if the worksheet has any data, if yes then process
   if (defined $oBook->{Worksheet}[0]) 
   {
      $filename = $datapath . @filesplit[0] . ".csv";
      open(OUT, ">" . $filename) || die "Could not open ".$filename."\n";    
      
      for(my $xcount = $oWkS->{MinRow}; 
         defined $oWkS->{MaxRow} && $xcount <= $oWkS->{MaxRow}; $xcount++) { 
         if ( $xcount <= 2 ) {
             next;
         }
         #for(my $ycount = $oWkS->{MinCol} ; 
         for(my $ycount = 0 ; 
            defined $oWkS->{MaxCol} && $ycount <= $oWkS->{MaxCol} ; $ycount++) { 
                $oWkC = $oWkS->{Cells}[$xcount][$ycount]; 
                print OUT $oWkC->Value, if($oWkC); 
                print OUT "|", if($ycount < $oWkS->{MaxCol});
         }  # end columns for
         print OUT "\n";
      } #end rows for
      close(OUT);
   } # end if
#} # end foreach worksheet

Open in new window

0
WebspeederAuthor Commented:
OK, I've got it. I think I was missing the semi-colon before. Now I have output. Thanks for your quick response.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux Distributions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.