CODEWITHSUNDEEP
excelfileperltextextract
Basil
Basil

Reputation: 47

Automate values from text files to each sheet of excel file

Is there any way to automate values from multiple text files to multipe worksheets in the same excel file? (Ex: Textfile1 values to Sheet1, Textfile2 values to Sheet2 and so on) Because for now I have to change the name of the text file and the excel worksheet everytime I extract values from different text files so that I need an automation to ease the work. Below is the recent Perl script I am using.

use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my $input_fn = 'accuracy_final_copy.txt.gz';
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";
 
    $worksheet->write( 0, 0,  "Accuracy value" );

    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row++;
    }
    $workbook->close();
}

Upvotes: 1

Views: 95

Answers (2)

zdim
zdim

Reputation: 66881

That's mostly all good what you have, just add a suitable loop

use warnings;
use strict;
use feature 'say';

use Excel::Writer::XLSX;

my @files = @ARGV ? @ARGV : die "Usage: $0 filenames\n";

my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );

for my $file (@files) {
    say "Processing $file";

    my $worksheet = $workbook->add_worksheet();

    my ($fname) = $file =~ m{(?:.*/)?(.*)};  # extract basename
    $worksheet->write( 0, 0,  $fname );

    open my $fh, '<', $file or do { 
        warn "Can't open $file: $!"; 
        next
    };

    my ($col, $row) = (0, 1);
    while (<$fh>) { 
        chomp;
        my $value = $_; 
        $worksheet->write( $row, $col, $value );

        ++$row;
    }   
}
$workbook->close;

This writes a new worksheet for each file, with one row per line, in the first column.

Upvotes: 1

H&#229;kon H&#230;gland
H&#229;kon H&#230;gland

Reputation: 40758

Here is an example which adds all files in the current directory ending with .gz as Sheet1, Sheet2, ..., etc. to the result.xlsx file:

use feature qw(say);
use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsx';
    my @input_files = <*.gz>;
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    for my $input_fn (@input_files) {
        say "Adding file $input_fn ..";
        add_sheet($input_fn, $workbook);
    }
    $workbook->close();
}

sub add_sheet {
    my ($input_fn, $workbook) = @_;

    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";

    $worksheet->write( 0, 0,  "Accuracy value" );
    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row++;
    }
}

Upvotes: 0

Related Questions