how to read all text files and write it into one excel with multiple tab

[johnyc]

Hi

i wish to read and write all wanted text files in one directory into one single excelsheet which contains of multiple worksheet that represent each file. my current script only can write out into many excelsheet instead of combining all into one. Anybody has idea to improve my script to achieve that purpose?
thank you!

    use Spreadsheet::WriteExcel;
    use Spreadsheet::ParseExcel;

    my @files = <*>;
    my @files=();

    while (<*>) 
    {
      push (@files,$_) ;
    }

    foreach $get (@files)
    {

    open(I, $get) || die "Can't open file\n";

    while(!eof(I)) {

        $line=<I>;
        $line=$`            if ($line=~/\s$/);
        push (@wanted, $line);

    }

    close(I);

    $get=~s/\.txt//;
    open ($OUT, ">$get.xls");

        $workbook  = Spreadsheet::WriteExcel->new($OUT);
        $worksheet= $workbook->add_worksheet($get);

    foreach $display(@wanted)
    {
    $worksheet->write($row, 0, "$display");
    $row++;
    }

    $workbook->close();
    close($OUT);
    @wanted=();
    }

[2teez]

Hi,
It easily done, but you are going about it the wrong way.
In the first place you don't need Spreadsheet::ParseExcel since you are not parsing excel sheets. Then again why read into an array, only to empty it again.
Lastly, you are going over each file and make a new excel workbook, that is why you have your program generating several excel workbook with a single worksheet for each file.
Please check the script below, it should do what you wanted, but use it as a guide.

#!/usr/bin/perl
use warnings;
use strict;
use Cwd qw(abs_path);
use Spreadsheet::WriteExcel;

die "Please use thus: Perlscript.pl path_to_search " unless defined $ARGV[0];

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

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

chdir $path or die "no such directory: $!";
if ( -d $path ) {    ## test if $path given is a directory
    opendir my $dh, $path or die "can't open directory: $!";
    while ( defined( my $file = readdir($dh) ) ) {
        chomp $file;
        next if $file eq '.' or $file eq '..';
        ##  add new worksheet with different name each time
        (my $sheetname = $file) =~s/\.\w+?//; ## get file name
        my $wrksheet = $workbook->add_worksheet($sheetname);
        $wrksheet->write_col( 0, 0, [ @{ read_out_your_file($file) } ] );
    }
}

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

[johnyc]

Hi 2steez, thanks for the coding

[2teez]

Hi Johnyc,
If this works for you please make the trend as solved.
thanks

[Mayank_3]

my folder contains tsv file in which my data is tab seperated

Here in generated xls data is not in proper format
can u help me ??

[2teez]

Hi Mayank3,
You would probably want to start a NEW trend, since it looks like this has solved and the Original Poster has moved on. Moreover, your generated "xls" file is not shown any where? Yes of course we can help, but you will have to help us to help you.

Thanks.