Convert TXT to Excel with Data::Table::Excel the Perl module

    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 or other name. To use the script, do this:

    $ perl file01.txt file02.txt file03.txt ...
    Facebook Twitter LinkedIn LINE Skype EverNote GMail Yahoo Yahoo
    Facebook Facebook Twitter Parler