Automate values from text files to each sheet of excel file

user_17108014 0
Viewed 85

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();
}
extract perl excel text file
2 Answers

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.

user_46533790

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++;
    }
}
user_21737730
Related