Is there a way to easily take 3 text files and turn it into a multi-tab excel sheet via script?

Is there a way to easily take 3 text files and turn it into a multi-tab excel sheet via script?

files are file1.txt, file 2.txt, file3.txt - i would like it to output to excelsheet1.xls with 3 tabs.

Answers


You don't mention the format of the text files so real example code is difficult, but you can use Spreadsheet::WriteExcel for this. Look at the add_worksheet() method for creating new tabs.

Given that you say that each line is a number followed by text I am presuming that this is two columns per row with a space delimiting the first and second columns and only digits in the first column. If this is not true the regex below would need to be adjusted. That said, here's some sample code.

#!/usr/bin/env perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;

sub read_file{
        my $f = shift;
        my @row;
        open(my $fh, '<', $f) or die $!;
        while(<$fh>){
                chomp;
                s/^(d+)s+//; # assuming format of "1 Text heren2 More textn"
                if(defined $1){
                        push(@row, [$1, $_]);
                }
        }
        close($fh) or die $!;

        return @row;
}

if($#ARGV < 1){
        die "$0: file1 [file2 ... filen] output.xls\n";
}
my $xl = Spreadsheet::WriteExcel->new(pop);

foreach my $file (@ARGV){
        if( -f $file){
                my @rows = read_file($file);
                my $sheet = $xl->add_worksheet($file);
                for my $row (0..$#rows){
                        my @cols = @{$rows[$row]};
                        for my $col (0..$#cols){
                                $sheet->write($row, $col, $cols[$col]);
                        }
                }
        }
}

Input files are given on the command line and processed in order, turning each one in to a tab named after the file name. The output file name is given on the command line last, after one or more input file names.

EDIT: Now including the improvements FM mentioned in his comment and a trivial CLI for specifying the output file name.


Need Your Help

NSBitmapImageRep to NSMutableArray or NSMutableDictionary

objective-c opengl nsmutablearray nsmutabledictionary nsbitmapimagerep

Is it possible to store NSBitmapImageRep copy to NSMutableDictionary or NSMutableArray? I need to save copy of OpenGL texture somewhere so, that I could use it later. So if I would do glReadPixels,...

References in Java for Android

java android xml

Coming from Web development background, I need some help understanding some of the basic syntax used in Android Applications' Java development aspects: