CODEWITHSUNDEEP
excelperl
MikG
MikG

Reputation: 1019

Perl - Dropping delimited text files into one Excel file with tabs

Returning to Perl after some time off, I have been looking for a way to drop some tab delimited text files into an array and then into an Excel file; basically an Excel tab generated for each text file in a directory. Generally the text files are a similar format.

The code below which has been cobbled from examples generally produces what I am after. However the output ignores any tabs and prints all text (per row) in one string. I am struggling with how to implement the tab delimiter into the code. I know I will need to split the text files as they are pushed into the array. I had been playing around with hashes, but I think I am looking too far into the problem, and it's likely to be an obvious answer that I am missing.

use warnings;
use strict;
use Cwd qw(abs_path);
use Spreadsheet::WriteExcel;

die "Log path ARG required " unless defined $ARGV[0];

my $path = abs_path( $ARGV[0] );

my $workbook = Spreadsheet::WriteExcel->new("resultsbook.xls");

chdir $path or die "no such directory: $!";
if ( -d $path ) {    ## test if $path given is a directory
    opendir my $dir, $path or die "can't open the directory: $!";
    while ( defined( my $file = readdir($dir) ) ) {
        chomp $file;
        next if $file eq '.' or $file eq '..';

        (my $sheetname = $file) =~s/\.\w+?//; 
        my $wrksheet = $workbook->add_worksheet($sheetname);
        $wrksheet->write_col( 0, 0, [ @{ readfile($file) } ] );
    }
}

sub readfile {
    my $textfilecontent = [];
    open my $fh, '<', shift() or die "can't open file:$!";
    while (<$fh>) {
        chomp;
        push @{$textfilecontent}, $_, $/;
    }
    return $textfilecontent;
}

Upvotes: 0

Views: 351

Answers (1)

imran
imran

Reputation: 1560

You need to split the lines with tab (or whatever delimiter) before pushing them into the @textfilecontent variable. There are a couple of other minor corrections in here:

use warnings;
use strict;
use Cwd qw(abs_path);
use Spreadsheet::WriteExcel;

die "Log path ARG required " unless defined $ARGV[0];

my $path = abs_path( $ARGV[0] );

my $workbook = Spreadsheet::WriteExcel->new("resultsbook.xls");

chdir $path or die "no such directory: $!";
if ( -d $path ) {    ## test if $path given is a directory
    opendir my $dir, $path or die "can't open the directory: $!";
    while ( defined( my $file = readdir($dir) ) ) {
        chomp $file;
        next if $file eq '.' or $file eq '..';

        (my $sheetname = $file) =~s/\.\w+//; 
        my $wrksheet = $workbook->add_worksheet($sheetname);
        $wrksheet->write_col( 0, 0, readfile($file));
    }
}

sub readfile {
    my @textfilecontent = ();
    open my $fh, '<', shift() or die "can't open file:$!";
    while (<$fh>) {
      chomp;
      push @textfilecontent, [split(/\t/)];
    }
    return \@textfilecontent;
}

Upvotes: 3

Related Questions