scripting - converting an Excel (xls) file to a comma separated (csv) file without the GUI

Question

Is there a simple way to translate an XLS to a CSV formatted file without starting the Excel windowed application?

I need to process some Excel XLS workbooks with scripts. For this i need to convert the xls file into a csv file. This can be done with a save-as from the Excel application. But, i would like to automate this (so, not open the Excel application window).

It will suffice if the first sheet from the workbook gets translated to the CSV format. I need to just process data in that sheet.

I have Cygwin and Excel installed on my system -- if that helps.

Edit: Ok, i have a working solution with Perl. Updating for future use by others.

I installed the Spreadsheet::ParseExcel module. and then used read-excel.pl sample.

My code is a slight variation of this sample code, as below.

#!/usr/bin/perl -w
# For each tab (worksheet) in a file (workbook),
# spit out columns separated by ",",
# and rows separated by c/r.

use Spreadsheet::ParseExcel;
use strict;

my $filename = shift || "Book1.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
    print "#Worksheet $sheet: $sheetName
";
    next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCol})));
    foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
        foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
            if (defined $eSheet->{Cells}[$row][$column])
            {
                print $eSheet->{Cells}[$row][$column]->Value . ",";
            } else {
                print ",";
            }
        }
        print "
";
    }
}

---

Update: Here is a Powershell script that might also be easy to work with; as-is from this MSDN blog and, SO Reference.

$excel = New-Object -comobject Excel.Application
$workbooks = $excel.Workbooks.Open("C:est.xlsx")
$worksheets = $workbooks.Worksheets
$worksheet = $worksheets.Item(1)
$range = $worksheet.UsedRange
foreach($row in $range.Rows)
{
    foreach($col in $row.Columns)
    {
        echo $col.Text
    }
}

---

Update: I recently came across a Windows tool CSVed at this Superuser answer which might be useful to some people.

Answer 1

You can use xls2csv from the catdoc package if you're on Debian/Ubuntu