Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

writing Header content only one time while merging multiple excel file 1

Status
Not open for further replies.
raghavanv

raghavanv

Programmer
Oct 5, 2008
12
GB
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:
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
 
Sort by date Sort by votes
You have this line

Code: 
next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));

That looks odd. I think you want

Code: 
next if $row_index == $source_sheet->{MinRow} && $first_file ne "no";

I can't see where you're changing $first_file from "yes" to "no". You could make it the very last line in this loop

Code: 
foreach my $stat_files_token (@stat_files) {
 # do everything you have
 $first_file = 'no';
}

Seems like that should work.

--
 
Upvote 0 Downvote
Thanks sycoogtit for your reply...

i did the changes what u have said but now i get the output without any header in output file.
ie,output without header

Code: 
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

all i need is to have header content one time in the output file.

so, what should i change in the script to get the desired output???

Regards,
Vijayarl
 
Upvote 0 Downvote
Oops. Looks like

Code: 
next if $row_index == $source_sheet->{MinRow} && $first_file ne "no";

should be

Code: 
next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";

You're also changing $first_file to 'no' at the end of the loop, right?

What's this line for?

Code: 
next if $row_index == 1;

Seems like that should be taken out. If you're still having problems, please post the new code.

--
 
Upvote 0 Downvote
hi syscoogtit,

i still get the same output..

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}) 
						#next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
						next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";
  							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();
	  $first_file="no";
	  
  }
  &write_output;
 #&call_sendmail;
  print "Processing Done. Time to analyse\n";

can you help me out ????

Regards,
Raghavanv
 
Upvote 0 Downvote
It looks like you're missing a '{' at the end of this line

Code: 
foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow})

It also looks like you're setting $first_file to 'no' outside of the loop. Try fixing your indentation so it's easier for you (and us) to read.

--
 
Upvote 0 Downvote
hi sycoogtit,

sorry for my identation...

Code: 
# Build an array of the stats type to be collated
@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 = $PARAM{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}){ 
next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";
 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}[$row_index][$col_index]; 
if (defined $source_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();
   					}							}
		}
	$first_file="no"; 
      $con_wb->close();
  
	}
  &write_output;
  print "Processing Done. Time to analyse\n";

i tried what u said but still getting same output.

can u help me ???

Regards,
Raghavanv
 
Upvote 0 Downvote
sorry again....

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}){ 
					
						#next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
						next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";
  							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}[$row_index][$col_index]; 
								if (defined $source_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();
   									}
					}
				}
	  $first_file="no"; 
      $con_wb->close();
	}
  &write_output;
 #&call_sendmail;
  print "Processing Done. Time to analyse\n";
 
Upvote 0 Downvote
Try 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}){
	    
            #next if $row_index eq (($source_sheet->{MinRow}) && ($first_file ne "no"));
            next if $row_index == $source_sheet->{MinRow} && $first_file eq "no";
            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}[$row_index][$col_index];
                if (defined $source_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();
            }
        }
        $first_file="no";
    }
    $con_wb->close();
}
&write_output;
#&call_sendmail;
print "Processing Done. Time to analyse\n";

--
 
Upvote 0 Downvote
Thanks Sycoogtit......(3*) - stars for you..:)

Now am getting the desired result...

Thanks once again for your patience reply.... :)

Regards,
Raghavanv
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rob54321
  • Locked
  • Question
Perl how to read line from file with interpolation 1
Replies
6
Views
378
rob54321
rob54321
newbie1238585
  • Locked
  • Question
Perl how to read string in csv file * as wildcard operator
Replies
2
Views
219
mikrom
mikrom
Limbomusic
  • Locked
  • Question
Perl files getting accessed directly
Replies
0
Views
181
Limbomusic
Limbomusic
Tester_V
  • Locked
  • Question
Need to print only the first line that has a match ...
Replies
2
Views
201
Tester_V
Tester_V
Tester_V
  • Locked
  • Question
Last match from a file
Replies
6
Views
257
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top