Solved

Why am I getting duplicate rows in excel

Posted on 2010-09-08
3
280 Views
Last Modified: 2012-05-10
Hi,

I am trying a way to copy one row at a time of an entire excel into a temporary excel file, in this way getting multiple excels and then pasting it back to the Final results excel immediately and doing some validations there by comparing the respective values with those returned from the database and finally displaying the results. I tried to increment the row variable once both items are validated from a Value Types sheet. However in the Final_Res.xls sheet, it is fine, if there is only 1 item to be validated. But if there are two items, it inserts an extra row. For Example, the validation in Row 1 worked fine, with CCY and BA_NID values colored which were compared. But Row 2 is extra, it should not have been there.

Here is the code I did, any pointers as to where I am going wrong? The code at first writes the individual temporary excel sheets(having one row each)  to the Final_Res.xls and then validates the rows by first picking up the columns from a Value types sheet, then checking whether these columns are there in the generated Final Results sheet and then taking their value and comparing with the database. I am also attaching The Value_Type sheet from which the columns are taken to be compared and the Final_Results sheet. I also find that duplicate intermediate sheets get generated when two items are used for validation but is fine when 1 item is used.

Pls help.


#!/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 $xls = shift;

my $le_nid = shift;

my $ba_nid = shift;

my $posting_event_type = shift;

my $audit_funding_rule = shift;

my $audit_acct_num = shift;

my $col_fmt = shift;



my $value_parser   = Spreadsheet::ParseExcel->new();

my $value_workbook = $value_parser->parse("Value_Type_Query1.xls");

my $value_worksheet =  $value_workbook->worksheet("Queries");





my ( $val_row_min, $val_row_max ) = $value_worksheet->row_range();

my ( $val_col_min, $val_col__max ) = $value_worksheet->col_range();



my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');



}









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

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

$row_min++ if $parser; # skip header row after first time

#$row_max = $parser ? 0 : 1;





my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls

#$row_max = $parser ? 0 : 1;



ROW:

for my $row ($row_min .. $row_max) {

COL:

    for my $col ($col_min .. $col_max) {

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

 

        next COL unless defined $cell;



        my $cv = $cell->value;



        my $header_cell =  $sql_worksheet->get_cell(0,$col);

         next COL unless defined $header_cell;

        $header_cell = $header_cell->value;

        my $dcol = $header_cell;

        

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

###################################################################   

         my $xls_cell = $worksheet->get_cell(0, $col);

         

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

         my $delta_col_val = $worksheet->get_cell($v_row, $col);  

         $delta_col_val = $delta_col_val->value;

         if ($v_row) {

         

         for my $val_row($val_row_min+1 .. $val_row_max) {

          my $val_del_col = $value_worksheet->get_cell($val_row,2);

          next unless defined $val_del_col;

          $val_del_col= $val_del_col->value;

           if (defined $val_del_col and $val_del_col eq "$dcol") {

            my $val_col_fmt = $value_worksheet->get_cell($val_row,3)->value;

            

           if (defined $val_col_fmt  and $val_col_fmt eq "INPUT"  and $val_col_fmt eq "$col_fmt" and  $value_worksheet->get_cell($val_row, 4)->value eq "Q" ) {



             my $query1 = $value_worksheet->get_cell($val_row, 5)->value;

            $query1 =~ s/\$le_nid/$le_nid/;

            $query1 =~ s/\$ba_nid/$ba_nid/;

            $query1 =~ s/\$posting_event_type/$posting_event_type/;

            $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

            $query1 =~ s/\$audit_acct_num/$audit_acct_num/;



            print "\n\nThe First Final Query is --\n\n $query1\n\n";

            

             my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or

          die "Can't connect to database: $DBI::errstr";





           my $sql = "$query1";





          my $sth = $dbh->prepare($sql);





         $sth->execute();



         while (my @data = $sth->fetchrow_array) {



         print "The value returned from the db is " . "@data\n";

            





           if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

#          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         }

        

        elsif (defined $val_col_fmt  and $val_col_fmt eq "N/A1"  and $val_col_fmt ne "N/A" and $val_col_fmt ne "INPUT" and  $value_worksheet->get_cell($val_row, 4)->value eq "Q" ) {

 

          my $query1 = $value_worksheet->get_cell($val_row, 5)->value;

            $query1 =~ s/\$le_nid/$le_nid/;

            $query1 =~ s/\$ba_nid/$ba_nid/;

            $query1 =~ s/\$posting_event_type/$posting_event_type/;

            $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

            $query1 =~ s/\$audit_acct_num/$audit_acct_num/;



            print "\n\nThe First Final Query is --\n\n $query1\n\n";



             my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or

          die "Can't connect to database: $DBI::errstr";





           my $sql = "$query1";





          my $sth = $dbh->prepare($sql);





         $sth->execute();



         while (my @data = $sth->fetchrow_array) {



         print "The value returned from the db is " . "@data\n";







           if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

#          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         }







        else {last;}

        }

        else {next;}

       }

      }

       else {next;}

     

    }

        else {next;}

#################################################################

        } else {

          $worksheet->write($v_row, $col, $cv);

#################################################################

         

          $workbook->close;

            # add formatting

            $parser  = Spreadsheet::ParseExcel::SaveParser->new();

            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

            $worksheet = $workbook->{Worksheet}[0];

            $format1 = $workbook->AddFormat(Fill => [0,11,0]);

            $format2 = $workbook->AddFormat(Fill => [1,10,0]);

            

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

               # $v_row++;         

                my $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($v_row) {

 

                for my $val_row($val_row_min+1 .. $val_row_max) {

                my $val_del_col = $value_worksheet->get_cell($val_row,2);

                next unless defined $val_del_col;

                $val_del_col= $val_del_col->value;

                if (defined $val_del_col and $val_del_col eq "$dcol") {

                 my $val_col_fmt = $value_worksheet->get_cell($val_row,3)->value;



                  if (defined $val_col_fmt  and $val_col_fmt eq "INPUT"  and $val_col_fmt eq "$col_fmt" and  $value_worksheet->get_cell($val_row, 4)->value eq "Q" ) {



                  my $query1 = $value_worksheet->get_cell($val_row, 5)->value;

                  $query1 =~ s/\$le_nid/$le_nid/;

                  $query1 =~ s/\$ba_nid/$ba_nid/;

                  $query1 =~ s/\$posting_event_type/$posting_event_type/;

                  $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

                  $query1 =~ s/\$audit_acct_num/$audit_acct_num/;



                  print "\n\nThe First Final Query is --\n\n $query1\n\n";



                 my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or

                 die "Can't connect to database: $DBI::errstr";





                 my $sql = "$query1";





                 my $sth = $dbh->prepare($sql);





                $sth->execute();



                while (my @data = $sth->fetchrow_array) {



                 print "The value returned from the db is " . "@data\n";

               

                if ($delta_col_val eq "@data" ) {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

#                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            

              }

             }



               elsif (defined $val_col_fmt  and $val_col_fmt eq "N/A1"  and $val_col_fmt ne "N/A" and $val_col_fmt ne "INPUT" and  $value_worksheet->get_cell($val_row, 4)->value eq "Q" ) {



          my $query1 = $value_worksheet->get_cell($val_row, 5)->value;

            $query1 =~ s/\$le_nid/$le_nid/;

            $query1 =~ s/\$ba_nid/$ba_nid/;

            $query1 =~ s/\$posting_event_type/$posting_event_type/;

            $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;

            $query1 =~ s/\$audit_acct_num/$audit_acct_num/;



            print "\n\nThe First Final Query is --\n\n $query1\n\n";



             my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or

          die "Can't connect to database: $DBI::errstr";





           my $sql = "$query1";





          my $sth = $dbh->prepare($sql);





         $sth->execute();



         while (my @data = $sth->fetchrow_array) {



         print "The value returned from the db is " . "@data\n";







           if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

#          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         }





               else {last;}

            }

               else {next;}

           }



         }

           else {next;}

           

       }

            else {next;}

#################################################################

        }

    }

    

    $v_row++;

    

}





##################################################################################









################################################################################

$workbook->close() unless $parser;

$workbook->SaveAs($xls_res) if $parser;

Open in new window

Final-Res.xls
Value-Type-Query1.xls
0
Comment
Question by:sunny82
  • 2
3 Comments
 

Author Comment

by:sunny82
ID: 33630757
Anybody pls help...I am stuck with this for long...
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 33634898
sunny82,

I'm afraid I don't understand the code as it's alien to me but if you are copying and possibly deleting rows as you go, try doing it from the last row of data in a worksheet and working upwards. Generally in VBA that is the only sure way to avoid difficulties. Probably has nothing to do with the problems you are encountering - it was just a thought.

Patrick
0
 

Author Comment

by:sunny82
ID: 33637635
Actually what I am trying to do is --

1. The Int_Res.xls worksheet has only 1 row at a time. I have taken 1 row at a time from a worksheet and put it into this temporary worksheet and then uploading it into a final worksheet. This uploading into the final worksheet is also done 1 row at a time. When that row is uploaded/copied into the Fin_Res.xls worksheet, some validations are performed for each of its column values by comparing it with the database. If the values are correct, then marked as Passed and the corresponding column is colored in green, else it is marked Test Failed and colored in Red. Then the next row comes from the original worksheet and then put into a temporary worksheet and a final worksheet and the same process is repeated as above. The rows in the original worksheet are not getting deleted, just copied to a temporary worksheet one row ata  time so that I can work with 1 row for validations in the final worksheet.

The code above is used just for this validation after it is loaded into the Fnal_Res.xls worksheet.

2. How the validations work are this --- After a particular row is copied into the final worksheet, it goes through all the column headers in the temporary worksheet which is Int_Res.xls, stores it in a variable $dcol. Then it checks whether this $dcol is present in the Final worksheet and takes its corresponding column value and stores it in a variable $delta_col_val. Then it checks whether it finds $dcol in a Value_Type_Query1.xls worksheet which contains the names of columns to be validated and their corresponding validation queries. After it finds a match for $dcol in this worksheet, it fires the corresponding query and stores the result in an array @data. then it matches if $delta_col_val equal to @data and marks the particular column value in Red/Green and Test Passed/Test Failed.

Then it processes the next row from the original worksheet and repeats the steps 1 and 2

3. For this code, it is doing validations for the 2 columns BA_NID and LDGR_TYPE correctly but after writing the validation results in The Final worksheet, writes one duplicate row for the same in the next row which should not be there. Thus if you see The Final_Res.xls worksheet, row 2, 4, 6 etc are all duplicates.

I want to know why is this coming and what can be changed to solve this.
Pls let me know.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now