Webspeeder
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.
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I've got it. I think I was missing the semi-colon before. Now I have output. Thanks for your quick response.
ASKER
Open in new window