Link to home
Start Free TrialLog in
Avatar of Webspeeder
WebspeederFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of wilcoxon
wilcoxon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Webspeeder

ASKER

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

OK, I've got it. I think I was missing the semi-colon before. Now I have output. Thanks for your quick response.