perl  

Aug 16, 2014 • Michael Chen

If we want to convert comma- or space-seperated text files to Excel ones, we have to open Excel and do manual conversion. Is there automatic way to do batch conversion? Of course, you can do batch conversion without opening Excel with Data::Table::Excel.

Data::Table::Excel is a Perl module to convert between Data::Table objects and Excel (xls/xlsx) files. Data::Table reads CSV, SQL database and Excel files and converts them to CSV, HTML table and Excel files. That means we can convert between CSV files and Excel files.

Here it is a sample to use Data::Table::Excel. In the script, all TXT tables are converted to seperated sheets in one Excel files. You may modify the script to convert each TXT file to one Excel file.

#!/usr/bin/env perl

use strict;
use warnings;

use Text::CSV;
use Data::Table::Excel qw(tables2xls);

my $csv = Text::CSV->new({binary => 1, eol => "\n"}) or die "$!\n";

# Save table names for later use
my @table_names;

for my $file (@ARGV) {
    my $csv_file = $file;
    $csv_file =~ s/\.txt/.csv/;
    push @table_names, $csv_file;

    open my $FH_TXT, "<", $file or die "$!\n";
    open my $FH_CSV, ">", $csv_file or die "$!\n";

    while (my $line=<$FH_TXT>) {
	    my @cols = split /\s+/, $line;
	    $csv->print($FH_CSV, \@cols);
    }

    close $FH_TXT or die "$!\n";
    close $FH_CSV or die "$!\n";

}

my $xls_file = "output.xls";

my @tables;
for my $name (@table_names) {
    my $table = Data::Table::fromFile($name);
    push @tables, $table;
}

# tables2excel($fileName, $tables, $names, $colors, $portrait, $columnHeaders)
# $fileName: the Excel output file
# $table: an array ref for all sheets of tables
# $name: an array ref for sheet names
# $colors: an array ref for color array, each item has three elements
# $portrait: an array ref for portrait(0) or landscape(1)
# $columnHeader: an array ref of boolean to indicate whether to export column
#                headers for each table
my @portraits = map { 1 } @tables;
my @column_headers = map { 0 } @tables;
tables2xls($xls_file, \@tables, undef, undef, \@portraits, \@column_headers);

Save the script to convert.pl or other name. To use the script, do this:

$ perl convert.pl file01.txt file02.txt file03.txt ...