Friday, October 1, 2010

Perl Snippets Part 2 - CSV to XLS

This program will convert a CSV ( Comma Separated Value ) file to an XLS ( Windows Excel ) spreadsheet. The script should be provided with the CSV file name as the first command line argument. It takes an optional second argument as the output XLS file name. If no output filename is given, the output will be saved to out.xls in the current directory.

This script requires Spreadsheet::WriteExcel perl module. On my Ubuntu box, this was installed with the command:
safeer@penguinpower:~$sudo apt-get install libspreadsheet-writeexcel-perl

#!/usr/bin/perl -w

use Spreadsheet::WriteExcel;

if (!defined $ARGV[0]) { die "You must provide a csv file"; }
else {$CSV_FILE = $ARGV[0];}

if ( defined $ARGV[1] ) { $XLS_FILE = $ARGV[1]; }
else { $XLS_FILE = "out.xls"; }

unless ( open ( CSV,$CSV_FILE))
{ die "Unable to open ".$CSV_FILE;}

my $workbook = Spreadsheet::WriteExcel->new($XLS_FILE);
if ( !defined $workbook ) {
die "Unable to create excel file\n";
}
$worksheet = $workbook->add_worksheet();
$col = $row = 0;

while (<CSV>)
{
chomp;
my @line = split(",",$_);
for $cell (@line){
$worksheet->write($row, $col,$cell);
$col++;
}
$row++; $col = 0;
}
$workbook->close();
close (CSV);

No comments:

Post a Comment