Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

xls2tab - Simple MS Excel to TSV converter

by legato (Monk)
on Jan 10, 2005 at 19:07 UTC ( #421044=sourcecode: print w/replies, xml ) Need Help??
Category: Utility Scripts
Author/Contact Info Anima Legato <l3gatosan@gmail.com>
Description: Converts XLS data into TSV format, putting multiple sheets into separate files. Output files have a .tab extension. This is particularly useful for reading XLS files on non-MS platforms, and for bulk-loading data in XLS sheets into an RDMBS.

Tested on Windows 2000 and Linux.

Module requirements:

#!/usr/bin/perl

#~ Converter Script for XLS to TSV.  Handles Multiple Tabs into separa
+te files.
#~ (c)2004 Anima Legato <l3gatosan@gmail.com>
#~
#~ This code is redistributable and modifiable under the same terms as
+ Perl
#~ itself.

use strict;
use warnings;

use Spreadsheet::ParseExcel::Simple;
use File::Spec;

for (@ARGV) {
    for (glob $_) {
        next unless m/\.xls$/i;
        next unless -r $_;
        dump_books($_);
    }
}

sub dump_books {
    my ($vol, $path, $file) = File::Spec->splitpath(shift);
    my $eBook = Spreadsheet::ParseExcel::Simple->read(File::Spec->catp
+ath($vol,$path,$file));
    unless (defined $eBook) {
        warn "Can't open Spreadsheet in file $file (@".File::Spec->cat
+path($vol,$path,$file)."\n";
        return undef;
    }
    
    my @sheet = $eBook->sheets;
    for (0..@sheet-1) {
        next unless $sheet[$_]->has_data();
        my $sfn = $file;
        $sfn =~ s?\.xls$??i;
        $sfn.= ((@sheet > 1) ? sprintf(".%02i",$_) : "").'.tab';
        open TAB, '>', $sfn or do {
            warn "Unable to write to $sfn";
            next;
        };
        
        while ($sheet[$_]->has_data) { 
            my @row = $sheet[$_]->next_row;
            print TAB join("\t",@row)."\n";
        }
    }
} ##--dump_books--##

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://421044]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2021-06-21 04:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (98 votes). Check out past polls.

    Notices?