Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I copy one row at atime from 1 excel to another

Posted on 2010-08-27
13
Medium Priority
?
548 Views
Last Modified: 2012-06-21
How can I copy only 1 row from one excel file to another? Excel 1 may have 10 rows, but only 1 row at a time writes to Excel 2. So finally Excel 2 is formed with only the last row of Excel1 and all others overwritten. here is my code, this copies the whole excel 1 into 2--

Where is it wrong? Pls help me..

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

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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res3.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");


my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $workbook  = Spreadsheet::WriteExcel::Big->new("Temp_Res3_Int.xls");
my $worksheet = $workbook->addworksheet();



for my $row ( $row_min .. $row_max ) {
for my $col ($col_min .. $col_max) {

my $cell = $sql_worksheet->get_cell($row,$col);


$worksheet->write($row,$col,$cell->value);

#print $cell->value;


}
}

Open in new window

0
Comment
Question by:sunny82
[X]
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
  • 6
  • 4
  • 3
13 Comments
 
LVL 10

Expert Comment

by:jeromee
ID: 33546607
If all you care about is to copy the last row of excel 1 into the 1st row of Excel 2 try this:
for my $col ($col_min .. $col_max) {
   my $cell = $sql_worksheet->get_cell($row_max, $col);       # Read last row
   $worksheet->write(1, $col, $cell->value);               # write in to the 1st row

   #print $cell->value;
}
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 33546629
Line 41 needs to be $worksheet->AddCell rather than write.  You need to use the functions from Spreadsheet::ParseExcel::SaveParser if you want to edit an existing Excel.

Also, you need to do $workbook->SaveAs at the end of your code in order to save the changes.
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 33546667
Never mind, I was assuming you were using SaveParser for $workbook since you had it in your use section and you talk about copying from one file to another (rather than creating a second one based on the first).

To do what you want, I would change 35-47 to:

for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:sunny82
ID: 33546692
No,
what I wanted was -- The first row of Excel 1 will populate first row of Excel 2 and create the Excel 2 sheet, then second row of Excel 1 will again create Excel 2 sheet by becoming first row of Excel 2 and overwriting the previous sheet created and so on.

So after all the rows of Excel 1 are processed, we will be left with Excel 2 sheet only containing the last row of Excel 1. i do not want the last row specifically but all the rows created in Excel Sheet 2 like this, so that I can modify the Excel 2 sheet by calling some other script from this.

Thus as I explained above, Excel sheet 2 will only have 1 row at a time.

Is it possible? What do you think?
0
 

Author Comment

by:sunny82
ID: 33546742

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

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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res3.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");


my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $workbook  = Spreadsheet::WriteExcel::Big->new("Temp_Res3_Int.xls");
my $worksheet = $workbook->addworksheet();


for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}
0
 
LVL 10

Expert Comment

by:jeromee
ID: 33546763
I don't understand why you care about copying all those rows if they will eventually be overwritten by the last row?
0
 

Author Comment

by:sunny82
ID: 33546779
The above code only showed me the last row in sheet 2. But as I said earlier, I need to be see only 1 row at a time in sheet 2. Thus if I write a statement

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Next_Code.pl" inside


for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}

I should be able to see sheet 2 with row 1 of sheet 1 in it. Again when it scans  the next row
of sheet 1, my calling script can access sheet 2 with row 2 of sheet1 in it. In this way, it has to be done.

Is it possible?

0
 
LVL 10

Expert Comment

by:jeromee
ID: 33546822
Are you saying that you want to create excel 2 as many times as there rows in excel1 for the purpose of having excel 2 verified by another script?

If so, it's possible but you need to save excel 2 each time you create one row...
BTW, it seems highly inefficient.
0
 
LVL 27

Expert Comment

by:wilcoxon
ID: 33546875
I agree with jeromee.  What you are trying to do is *very* inefficient.

Is there some reason you can't loop over the rows in sheet 1 and then call the scripts using the values in the cells as arguments (or better just write them as another subroutine in the perl script)?

What you are trying to do is effectively create an entirely new Excel sheet with one row in it each time which has huge amounts of overhead (see discussion in SaveParser on why/how it's not possible to actually edit an xls file).
0
 

Author Comment

by:sunny82
ID: 33547486
Actually, yes. I know thats a very inefficient way. But thats what I am trying to do by using the excel sheet having only 1 row at a time and then calling my validation script

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl" . Here are the reasons why -

I tried to use only 1 excel for data validation purposes by calling the validation script from the block above, but it was validating with all the rows for a particular column value rather than the column value for that particular row.. Pls see the link below for details --

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

So, now basically what I need is, If Excel 1 has 10 rows say, I want to cut and paste the 1st row from Excel 1 to an intermediate excel sheet, then do my data validation and then write it to a final excel, say Final_Results.xls. Then I will cut and paste the second row of Excel 1, put it into an intermediate excel, do data validations and then append it to that Final_Results.xls. Even the cut is not a mandatory thing, it can be ignored. The data validation is working fine, except that as I said above it needs to verify a particular column value in that same row instead of validating it with each and every row. Thats the reason why, I want to do the validation in an intermediate script which will have just 1 row and then append it to a Results sheet.

Pls let me know how I do it here in the code above by creating an entirely new excel sheet i.e. an intermediate excel sheet, for every row of excel 1 so that I can do the validations there and then append it to a final results sheet after the validations are done.

Or any better solution to the problem at http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26435242.html

Anything will do..Pls help me as I am stuck with this for 3-4 days now..

0
 
LVL 10

Accepted Solution

by:
jeromee earned 2000 total points
ID: 33547814
Maybe you should take a look at you validating script (/ms/user/s/sayantag/Val_Code.pl) and see what it does in order to incorporate its validation logic to the script that you are trying to right.
Otherwise you have to do the creation, saving and validation of Excel2 each time you loop thru yourr outter (row) loop.
Something like this... (I didn't test it)

my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
   my $workbook  = Spreadsheet::WriteExcel->new("Temp_Res3_Int.xls");
   my $worksheet = $workbook->addworksheet();

   for my $col ($col_min .. $col_max) {
      my $cell = $sql_worksheet->get_cell($row,$col);
      $worksheet->write($row,$col,$cell->value);
   }

    $workbook->close()
    system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl" . Here are the reasons why -
}

Good luck!
0
 

Author Comment

by:sunny82
ID: 33548175
I will let you know on Monday if that worked and if I need more ..Many thanks.
0
 

Author Comment

by:sunny82
ID: 33567971
This worked fine..Thx so much...I am closing this question...
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

610 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