Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-09-01
13
856 Views
Last Modified: 2013-12-26
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
Comment
Question by:sunny82
  • 8
  • 4
13 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33580412
If you need a PERL script, you'd better post this in the Perl section as well.
0
 

Author Comment

by:sunny82
ID: 33580537
I added the original question zone as perl primary zone and then excel and then unix zone..
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 33580802
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:sunny82
ID: 33581957
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33581965
Typo.  It should be:

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

Author Comment

by:sunny82
ID: 33582035
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
 

Author Comment

by:sunny82
ID: 33582207
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33583332
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
 

Author Comment

by:sunny82
ID: 33583794
Thx...I will try this tomorrow and send you the attached excel file as I do not have remote access now..
0
 

Author Comment

by:sunny82
ID: 33587320
Here is an intermediate excel sheet...
Int-Res9-52-46.xls
Int-Res9-52-48.xls
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33587752
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
 

Author Comment

by:sunny82
ID: 33588187
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
 

Author Comment

by:sunny82
ID: 33589871
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel 2007 Comparing Cell Contents with IF Statements 12 36
copy same as above data 18 36
Web Query 1 20
vba delte many shapes with same name 4 9
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question