sunny82
asked on
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/a utomation_ 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/a utomation_ 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..
I need to write a perl script for the following --
I have some .xls files in the directory "/v/region/na/appl/qatools
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
Is it possible? Pls help..
If you need a PERL script, you'd better post this in the Perl section as well.
ASKER
I added the original question zone as perl primary zone and then excel and then unix zone..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
getting this error --
Can't locate object method "workbook" via package "Spreadsheet::ParseExcel:: Workbook" at /ms/user/s/sayantag/MoveIn tExcel5.pl line 50.
at /ms/user/s/sayantag/MoveIn tExcel5.pl line 30
main::__ANON__('Can\'t locate object method "workbook" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIn tExcel5.pl line 50
Can't locate object method "workbook" via package "Spreadsheet::ParseExcel::
at /ms/user/s/sayantag/MoveIn
main::__ANON__('Can\'t locate object method "workbook" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIn
#!/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);
}
}
}
Typo. It should be:
my $worksheet = $workbook->worksheet(0);
my $worksheet = $workbook->worksheet(0);
ASKER
Still the same
Can't locate object method "workheet" via package "Spreadsheet::ParseExcel:: Workbook" at /ms/user/s/sayantag/MoveIn tExcel5.pl line 50.
at /ms/user/s/sayantag/MoveIn tExcel5.pl line 30
main::__ANON__('Can\'t locate object method "workheet" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIn tExcel5.pl line 50
I tried both
my $worksheet = $workbook->worksheet(0);
as well as
my $worksheet = $workbook->worksheet("Delt a");
since the sheet is named "Delta" in the excel files in the directory.
Can't locate object method "workheet" via package "Spreadsheet::ParseExcel::
at /ms/user/s/sayantag/MoveIn
main::__ANON__('Can\'t locate object method "workheet" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIn
I tried both
my $worksheet = $workbook->worksheet(0);
as well as
my $worksheet = $workbook->worksheet("Delt
since the sheet is named "Delta" in the excel files in the directory.
ASKER
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
final-res.xls
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).
ASKER
Thx...I will try this tomorrow and send you the attached excel file as I do not have remote access now..
ASKER
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.
ASKER
Pls do not worry about this, this was only a back up for
https://www.experts-exchange.com/questions/26442490/How-do-I-move-rows-from-one-sheet-to-another.html
If the question at the link above gets solved, I will close this question and award you the points for the earlier solution.
https://www.experts-exchange.com/questions/26442490/How-do-I-move-rows-from-one-sheet-to-another.html
If the question at the link above gets solved, I will close this question and award you the points for the earlier solution.
ASKER
The question at
https://www.experts-exchange.com/questions/26442490/How-do-I-move-rows-from-one-sheet-to-another.html
got resolved, so I am closing this question also.
https://www.experts-exchange.com/questions/26442490/How-do-I-move-rows-from-one-sheet-to-another.html
got resolved, so I am closing this question also.