Excel To Tab Delimited using Spreadsheet::ParseExcel

[upallnight]

Run from command line. Simply parses an excel file and saves it as a tab-delimited file.

#!/usr/local/bin/perl

######################
#    Author: Alan Hamlett
#    Date: 03/18/2009
######################

use strict;
use Spreadsheet::ParseExcel;

my $inputFile    = shift;
my $outputFile    = shift;

if(!-e $inputFile || !$outputFile) {
    print qq~
Usage: excelToTabDelim inputFile outputFile
    Parses an excel input file into a tab delimited out file
~;
    exit 1;
}

if(open(OUT, "> $outputFile")) {
    my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($inputFile
+);
    foreach my $worksheet (@{$workbook->{Worksheet}}) { # looping thro
+ugh worksheets
        for(my $row = $worksheet->{MinRow}; defined $worksheet->{MaxRo
+w} && $row <= $worksheet->{MaxRow}; $row++) { # loop through rows
            my @line;
            for(my $column = $worksheet->{MinCol}; defined $worksheet-
+>{MaxCol} && $column <= $worksheet->{MaxCol}; $column++) { # loop thr
+ough columns
                my $value = $worksheet->{Cells}[$row][$column] ? $work
+sheet->{Cells}[$row][$column]->Value : "";
                $value =~ s/^\s+//;
                $value =~ s/\s+$//;
                $value =~ s/[\r\n]+//g;
                $value = " " if $value ne "0" && !$value;
                push(@line, $value);
            }
            $" = "\t";
            print OUT "@line\n";
        }
    }
    close(OUT);
}
else {
    print "Error: Could not write to output file '$outputFile'\n";
}

[toolic]

See also xls2csv on CPAN which outputs a CSV file (commas instead of tabs).

[vijax]

good work buddy....i am going to try this

[Anonymous Monk]

How can I retain empty cells? My report starts on the 6th column. When I use this, it removes the empty cells. I would want to have 5 tabs representing the first 5 empty columns.

[graff]

A similar script that I posted here a couple years prior to the one above preserves empty cells that occur to the left of non-empty cells. (It also handles unicode character content, in case that's relevant.) Here: xls2tsv

[Anonymous Monk]

use the option that is documented to stop doing that :) (hint, read the "f"riendly docs)