Hi All,
Am newbiee to perl.. i have a requirement & am trying to acheive this from past few weeks but all went in vain..
Requirement:
1.There are few stat's excel files,each of these stat files will have header content & followed by data.
all i need is to merge all same stat's files into single stat file which should have only one header.
Stat's file look like this (input files):
1.filename:iostat-xn-20080118-1800.xls
2.there content will be:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
3.file number 2 :
File Name:iostat-xn-20080118-1900.xls
There Contents:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
My output file should look like this:
FileName:iostat-xn.xls
There Contents:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
instead of this ouput,i get output file with header written twice. ie,
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
i have able to merge all the same type stat's files into single file but failed to avoid writing the multiple header content in the output file.
script goes like this:
please can anyone guide me on this ????
Thanks,
Raghavanv
Am newbiee to perl.. i have a requirement & am trying to acheive this from past few weeks but all went in vain..
Requirement:
1.There are few stat's excel files,each of these stat files will have header content & followed by data.
all i need is to merge all same stat's files into single stat file which should have only one header.
Stat's file look like this (input files):
1.filename:iostat-xn-20080118-1800.xls
2.there content will be:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
3.file number 2 :
File Name:iostat-xn-20080118-1900.xls
There Contents:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
My output file should look like this:
FileName:iostat-xn.xls
There Contents:
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
instead of this ouput,i get output file with header written twice. ie,
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 18:00:00 0.5 2.9 15 30.5
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 0.4 2.9 13.7 30.5
18/01/2008 18:00:00 0.1 0 1 0.1
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:00 1 0.7 36.8 18.9
18/01/2008 18:00:00 0 0 0 0
18/01/2008 18:00:30 0.5 11.8 14.9 112.6
DD/MM/YYYY HH:MM:SS r/s w/s kr/s kw/s
18/01/2008 19:00:00 0.5 2.9 15 30.5
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 0.4 2.9 13.7 30.5
18/01/2008 19:00:00 0.1 0 1 0.1
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:00 1 0.7 36.7 19
18/01/2008 19:00:00 0 0 0 0
18/01/2008 19:00:30 0.6 12.1 23.8 115.3
i have able to merge all the same type stat's files into single file but failed to avoid writing the multiple header content in the output file.
script goes like this:
Code:
# Build an array of the stats type to be collated
#my @stat_type=split(/:/, $PARAMS{COLLATE_STAT_TYPES});
@stat_type=@uniq;
# for each of the stats type, read the xls of each hour and write into a consolidated xls.
foreach my $stat_type_token (@stat_type){
my $con_wb = Spreadsheet::WriteExcel::Big->new("$PARAMS{INPUT_FILE_BASE_PATH}$stat_type_token.xls");
my $con_excel = $con_wb->add_worksheet();
my $con_row = 0;
my @stat_files=split(/:/, $PARAMS{COLLATE_YYYYMMDD_HH});
my $first_file="yes";
foreach my $stat_files_token (@stat_files) {
my $source_xl_name = $PARAMS{INPUT_FILE_BASE_PATH}.$stat_type_token."-".$stat_files_token.".xls";
my $source_excel = new Spreadsheet::ParseExcel;
my $source_book = $source_excel->Parse($source_xl_name) or die "Could not open source Excel file $source_xl_name !";
my $source_sheet = $source_book->{Worksheet}[0];
print "Processing $stat_type_token-$stat_files_token.xls please wait\n";
foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow}) {
#-p -e next if ~/^DD\/MM\/YYYY/i;
next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
next if $row_index == 1;
my $con_col = 0;
foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol}){
my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
my $head_cell = $source_sheet->{Cells}[0][$col_index];
if (defined $source_cell) {
#unless(($source_cell->Value ne $head_cell -> Value) && ($source_cell eq $head_cell)) {
$con_excel->write($con_row, $con_col, $source_cell->Value );
# }
}
$con_col++;
}
$con_row++;
$consol_rows++;
# max row count in Excel is 65536 rows.
# open a new sheet if the max row is reached.
if ( $consol_rows > 65000 ) {
$consol_rows = 0;
$con_row = 0;
$con_col = 0;
$con_excel = $con_wb->add_worksheet();
}
}
}
$con_wb->close();
}
please can anyone guide me on this ????
Thanks,
Raghavanv