Converting XLS Files To CSV With Perl

I was recently tasked with writing a small script that would take an XLS file:

and turn it into something more cross-platform friendly, like a CSV:

As the requester uses Windows XP, I was able to take advantage of Win32::OLE.

Here’s the code, followed by an explanation:


## CODE STARTS HERE ##
#!/usr/bin/perl -w
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const ‘Microsoft Excel’;
$Win32::OLE::Warn = 3;
my $Excel = />|| ‘Quit’);
my $Book = />my $Sheet = $Book->Worksheets(1);
foreach my $row (1..5)
{
foreach my $col (1..3)
{
next unless defined />my $print = />open (TXTFILE, ‘>>C:/Path/To/Output.csv’);
printf TXTFILE $print . “,”;
}
printf TXTFILE “\n”;
}
## CODE ENDS HERE ##
Now, as to what the hell this all means:


##
my $Excel = />|| ‘Quit’);
my $Book = />##

This section simply defines the excel application itself as a variable, to be used later. The latter part, near $book, selects the file we want to use; in this case,

##
my $Sheet = $Book->Worksheets(1);
##

Here we define “Sheet” to be $Book’s worksheet that we want to work with. In this case, we want Sheet 1. You can do this by name as well.

##
foreach my $row (1..5)
{
foreach my $col (1..3)
{
##

This is where we define which rows and columns we want to work with. $row (1..5) says you want to select everything from row 1, to row 5. $col (1..3) says we want to take the first 3 columns; in the case of an Excel file, this is the same as saying columns A, B, and C. This is the part I’ll next modify, to select certain columns and all populated rows, as we all know Excel files are rarely the same number of rows each time.


##
next unless defined />##

This instructs perl to skip empty cells


##
my $print = />open (TXTFILE, ‘>>C:/Path/To/Output.csv’);
##

Here we define the variable $print, and set it to equal the value of the selected row and column. We then have it prepare to insert into Output.csv by opening that file, and labeling it “TXTFILE” for later.


##
printf TXTFILE $print . “,”;
}
printf TXTFILE “\n”;
}
##

The last part of our script tells perl to print to TXTFILE (remember, this is just C:/Path/To/Output.csv) the value of $print, or the current value of the current row/column we’re working with. The “.” concats/combines $print with the value “,” which puts a comma after each column value in a specific row. We then finish the second foreach statement with } and begin our next line; printf TXTFILE “\n”; which simply instructs perl to go to a new line in the CSV after every column value has been printed to the csv with a comma after it.

Hopefully this has helped save you a few minutes; I will post the next bit of code (reading every non-blank row) in the next day or two.