[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

How do I append excel sheets into a single excel file by grepping on file creation time

Hi,

I need to write a perl script for the following --

I have some .xls files in the directory "/v/region/na/appl/qatools/util/data/reports/automation_framework/Results"


the .xls files have the structure like below

header1  header2  header3
value1      value2     value3

having a single row in each of them

I need to concatenate the .xls files into a final_res.xls file in a single sheet by first grepping on files beginning with "Int_Res" in the dir "/v/region/na/appl/qatools/util/data/reports/automation_framework/Results", then putting the file which was created first in the final_res.xls along with header, then appending the next created file but stripping off the header row which is row 0 and so on.

Is it possible? Pls help..
0
sunny82
Asked:
sunny82
  • 8
  • 4
1 Solution
 
calacucciaCommented:
If you need a PERL script, you'd better post this in the Perl section as well.
0
 
sunny82Author Commented:
I added the original question zone as perl primary zone and then excel and then unix zone..
0
 
wilcoxonCommented:
It's definitely possible.  I think this will work...
#!/usr/local/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;

my $dir = '/v/region/na/appl/qatools/util/data/reports/automation_framework/Results';

# get the files sorted by date
opendir DIR, $dir or die "could not open dir $dir: $!";
my @files = sort { (-M "$b") <=> (-M "$a") } grep { /\.xls/ and -f $_ } readdir DIR;
closedir DIR;

# create the new file
my $xls = Spreadsheet::WriteExcel->new("$dir/final_res.xls") or die "could not create final_res.xls: $!";
my $sheet = $workbook->add_worksheet();
my $frow = 0;

for my $i (0..@files-1) {
    my $parser = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->Parse("$dir/$files[$i]") or die "could not parse $files[$i]: $!";
    my $worksheet = $workbook->workbook(0);
    my ($row_min, $row_max) = $worksheet->row_range();
    my ($col_min, $col_max) = $worksheet->col_range();
    $row_min++ if $i; # skip header after first
    for my $row ($row_min..$row_max) {
        for my $col ($col_min..$col_max) {
            my $cell = $worksheet->get_cell($row, $col);
            next unless defined($cell);
            $sheet->write($frow, $col, $cell->value);
        }
    }
}

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sunny82Author Commented:
getting this error --

Can't locate object method "workbook" via package "Spreadsheet::ParseExcel::Workbook" at /ms/user/s/sayantag/MoveIntExcel5.pl line 50.
 at /ms/user/s/sayantag/MoveIntExcel5.pl line 30
        main::__ANON__('Can\'t locate object method "workbook" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIntExcel5.pl line 50

#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };



my $dir = '/v/region/na/appl/qatools/util/data/reports/automation_framework/Results';

# get the files sorted by date
opendir DIR, $dir or die "could not open dir $dir: $!";
my @files = sort { (-M "$b") <=> (-M "$a") } grep { /\.xls/ and -f $_ } readdir DIR;
closedir DIR;

# create the new file
my $workbook = Spreadsheet::WriteExcel->new("$dir/final_res.xls") or die "could not create final_res.xls: $!";
my $sheet = $workbook->add_worksheet();
my $frow = 0;

for my $i (0..@files-1) {
    my $parser = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->Parse("$dir/$files[$i]") or die "could not parse $files[$i]: $!";
    my $worksheet = $workbook->workbook(0);
    my ($row_min, $row_max) = $worksheet->row_range();
    my ($col_min, $col_max) = $worksheet->col_range();
    $row_min++ if $i; # skip header after first
    for my $row ($row_min..$row_max) {
        for my $col ($col_min..$col_max) {
            my $cell = $worksheet->get_cell($row, $col);
            next unless defined($cell);
            $sheet->write($frow, $col, $cell->value);
        }
    }
}

Open in new window

0
 
wilcoxonCommented:
Typo.  It should be:

my $worksheet = $workbook->worksheet(0);
0
 
sunny82Author Commented:
Still the same

Can't locate object method "workheet" via package "Spreadsheet::ParseExcel::Workbook" at /ms/user/s/sayantag/MoveIntExcel5.pl line 50.
 at /ms/user/s/sayantag/MoveIntExcel5.pl line 30
        main::__ANON__('Can\'t locate object method "workheet" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIntExcel5.pl line 50


I tried both

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

as well as

my $worksheet = $workbook->worksheet("Delta");

since the sheet is named "Delta" in the excel files in the directory.
0
 
sunny82Author Commented:
Sorry solved the above error, it was "workheet"  instead of worksheet..but the excel getting generated does not contain any data rows, just the column names for only 1 and Test Failed comes at the top. Attached is the excel generated..
final-res.xls
0
 
wilcoxonCommented:
Can you attach one of the intermediate xls files to this question?  I'm going to have to do some real debugging to figure out the issue (guessing something simple that isn't quite right).
0
 
sunny82Author Commented:
Thx...I will try this tomorrow and send you the attached excel file as I do not have remote access now..
0
 
sunny82Author Commented:
Here is an intermediate excel sheet...
Int-Res9-52-46.xls
Int-Res9-52-48.xls
0
 
wilcoxonCommented:
Unfortunately, I'm not sure what's wrong (the code looks right) and I'm not going to have a chance to look at this further for a while.
0
 
sunny82Author Commented:
Pls do not worry about this, this was only a back up for

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26442490.html

If the question at the link above gets solved, I will close this question and award you the points for the earlier solution.
0
 
sunny82Author Commented:
The question at

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26442490.html

got resolved, so I am closing this question also.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now