Solved

Why am I getting duplicate rows in excel

Posted on 2010-09-08
3
285 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel calculate based on 'x' in column 2 25
Excel Question 17 15
Excel - DATEDIF error #NUM 6 21
vba autofilter in row 4 6 9
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 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