Solved

How do I solve this Perl Spreadsheet problem

Posted on 2010-08-27
4
459 Views
Last Modified: 2012-05-10
Hi,

I have a problem. I have an excel sheet Temp_Res.xls. I want to parse the sheet like this --

It will take the some col values from the first row, go to another code, fire a database query, get another col value from the query and search the same column in the same row. If values are same, mark the same row as "Pass", otherwise "Fail". then it will go to the next row and do the same and so on.

The problem coming is the value retrieved from the database is searched in all the rows of the same col. So if I get BA_NID value as X from the query, then it will search BA_NID col in all the rows and mark every row as "Pass" or "Fail" according to the database value. Instead it should just check BA_NID col of first row to check for "Pass" or "Fail". Then after marking it in the same sheet, it should again take some col values from second row, fire query and come and check the corresponding column in only the 2nd row.

I know I am messing somewhere in the loop. But can't figure it out. Pls help.

In the third code, it is parsing "Temp_Res2.xls" which is nothing but a copy of "Temp_Res.xls". since I am not being able update the same sheet.

******************************************************************************************************************
================================================
below are Code 1 and Code 2 and attached is Code 3

======================================
Code 1
=======================================
#!/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::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_Res.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");


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

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

my $cell1 = $sql_worksheet->get_cell($row, 1);
my $cell39 = $sql_worksheet->get_cell($row, 39);
my $cell30 = $sql_worksheet->get_cell($row, 30);
my $cell6 = $sql_worksheet->get_cell($row, 6);
my $cell8 = $sql_worksheet->get_cell($row, 8);
my $cell16 = $sql_worksheet->get_cell($row, 16);

if (defined $cell1 and defined $cell39 and defined $cell30 and defined $cell6 and defined $cell8 and defined $cell16) {

my $posting_event_type =  $sql_worksheet->get_cell($row, 1)->value;
my $audit_funding_rule =  $sql_worksheet->get_cell($row, 39)->value;
my $audit_acct_num  = $sql_worksheet->get_cell($row, 30)->value;
my $ba_nid =  $sql_worksheet->get_cell($row, 6)->value;
my $le_nid =  $sql_worksheet->get_cell($row, 8)->value;
my $asset_id =  $sql_worksheet->get_cell($row, 16)->value;


my $actual_file2 = "/v/region/na/appl/qatools/util/data/reports/automation_framework/TgtFile/delta_LibraPrimeBrokerage.txt";
print "$audit_acct_num\n";

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Access_ETL_Rules1_Test.pl", $posting_event_type, $audit_funding_rule, $ba_nid, $le_nid, $asset_id,$audit_acct_num, $actual_file2);


}
}
=======================================================

=======================================================
Code 2
======================================================
#!/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::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 @etl_cc = @ARGV;

#print "$etl_cc[1]" . "\n";

my $posting_event_type = shift;

#"PBCLCASHPAY";

#shift;

#shift;
#my $ledger_type = shift;
my $audit_funding_rule = shift;

#"PB1";

#shift;
#shift;
my $ba_nid = shift;

#10589946;
#shift;
my $le_nid = shift;

my $asset_id = shift;

my $audit_acct_num = shift;
#20000050;
#shift;
#my $sf = shift;
my $actual_file2 = shift;

#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift;

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

my $format1 = $workbook->add_format();
my $format2 = $workbook->add_format();
my $format3 = $workbook->add_format(num_format => 'mm/dd/yy');


$format1->set_size(12);
$format1->set_bold();
$format1->set_color('blue');
$format1->set_align('center');
$format1->set_bg_color('yellow');



my $col=0;
my $row=0;

foreach my $i(

 "FUNDING_RULE_NME"
,"INPUT_VALUE_SIGN"
,"POSTING_EVENT_TYPE_CDE"
,"CHANGE_CATEGORY_ID_VAL"
,"AQUISITION_DTE_VAL"
,"AQUISITION_DTE_LPT_ID_VAL"
,"DUE_DTE_VAL"
,"DUE_DTE_LPT_ID_VAL"
,"CCY_VAL"
,"TRNSLTED_CCY_VAL"
,"ASSET_ID_VAL"
,"EXT_CNTRPRTY_ID_VAL"
,"BA_CNTRPRTY_ID_VAL"
,"INTERCPNY_CNTRPRTY_ID_VAL"
,"REGION_CNTRPRTY_ID_VAL"
,"CLIENT_ID_VAL"
,"UOM_CCY_VAL"
,"PRIMARY_FCP_ID"
,"MULTIPLIER_FTR"
,"LDGR_TYPE_CDE"
,"LAST_UPDT_ID"
,"LAST_UPDT_DTE_TME"
,"CLEARING_HOUSE_CDE_VAL"
,"SEC_POOL_ID_VAL"
,"TICKET_REF_NO_VAL"
,"MASTER_TICKET_REF_NO_VAL"
,"LEG_NME_VAL"
,"NETTING_GRP_ID_VAL"
,"COLLATERAL_GRP_ID_VAL"
,"PROD_GRP_VAL"
,"HEADER_ID_VAL"
,"DEBT_TENURE_NME_VAL"
,"BA_NID_VAL"
,"REGION_NID_VAL"
,"LE_NID_VAL"


)


 
{

$worksheet->write(0, $col++, $i, $format1);

}


my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";


my $query =
"SELECT * FROM PFND.ETL_RULES er where er.POSTING_EVENT_TYPE_CDE = '$posting_event_type' and er.FUNDING_RULE_NME like '$audit_funding_rule%'";


my $sql = "$query";

print "\n\nThe ETL Rules Table Query is $query" . "\n";



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

$sth->execute();



$row = 1;

while (my $a = $sth->fetchrow_hashref()) {

$worksheet->write($row,0, (defined($a->{"FUNDING_RULE_NME"})? $a->{"FUNDING_RULE_NME"} : '[NULL]'),  $format2);
$worksheet->write($row,1, (defined($a->{"INPUT_VALUE_SIGN"})? $a->{"INPUT_VALUE_SIGN"} : '[NULL]'), $format2);
$worksheet->write($row,2, (defined($a->{"POSTING_EVENT_TYPE_CDE"})? $a->{"POSTING_EVENT_TYPE_CDE"} : '[NULL]'), $format2);
$worksheet->write($row,3, (defined($a->{"CHANGE_CATEGORY_ID_VAL"})? $a->{"CHANGE_CATEGORY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,4, (defined($a->{"AQUISITION_DTE_VAL"})? $a->{"AQUISITION_DTE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,5, (defined($a->{"AQUISITION_DTE_LPT_ID_VAL"})? $a->{"AQUISITION_DTE_LPT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,6, (defined($a->{"DUE_DTE_VAL"})? $a->{"DUE_DTE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,7, (defined($a->{"DUE_DTE_LPT_ID_VAL"})? $a->{"DUE_DTE_LPT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,8, (defined($a->{"CCY_VAL"})? $a->{"CCY_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,9, (defined($a->{"TRNSLTED_CCY_VAL"})? $a->{"TRNSLTED_CCY_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,10,(defined($a->{"ASSET_ID_VAL"})? $a->{"ASSET_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,11,(defined($a->{"EXT_CNTRPRTY_ID_VAL"})? $a->{"EXT_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,12,(defined($a->{"BA_CNTRPRTY_ID_VAL"})? $a->{"BA_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,13,(defined($a->{"INTERCPNY_CNTRPRTY_ID_VAL"})? $a->{"INTERCPNY_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,14,(defined($a->{"REGION_CNTRPRTY_ID_VAL"})? $a->{"REGION_CNTRPRTY_ID_VAL"} :  '[NULL]'), $format2);
$worksheet->write($row,15,(defined($a->{"CLIENT_ID_VAL"})? $a->{"CLIENT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,16,(defined($a->{"UOM_CCY_VAL"})? $a->{"UOM_CCY_VAL"} : '[NULL]'), $format3);
$worksheet->write($row,17,(defined($a->{"PRIMARY_FCP_ID"})? $a->{"PRIMARY_FCP_ID"} : '[NULL]'), $format2);
$worksheet->write($row,18,(defined($a->{"MULTIPLIER_FTR"})? $a->{"MULTIPLIER_FTR"} : '[NULL]'), $format2);
$worksheet->write($row,19,(defined($a->{"LDGR_TYPE_CDE"})? $a->{"LDGR_TYPE_CDE"} : '[NULL]'), $format2);
$worksheet->write($row,20,(defined($a->{"LAST_UPDT_ID"})? $a->{"LAST_UPDT_ID"} : '[NULL]'), $format2);
$worksheet->write($row,21,(defined($a->{"LAST_UPDT_DTE_TME"})? $a->{"LAST_UPDT_DTE_TME"} : '[NULL]'), $format2);
$worksheet->write($row,22,(defined($a->{"CLEARING_HOUSE_CDE_VAL"})? $a->{"CLEARING_HOUSE_CDE_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,23,(defined($a->{"SEC_POOL_ID_VAL"})? $a->{"SEC_POOL_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,24,(defined($a->{"TICKET_REF_NO_VAL"})? $a->{"TICKET_REF_NO_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,25,(defined($a->{"MASTER_TICKET_REF_NO_VAL"})? $a->{"MASTER_TICKET_REF_NO_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,26,(defined($a->{"LEG_NME_VAL"})? $a->{"LEG_NME_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,27,(defined($a->{"NETTING_GRP_ID_VAL"})? $a->{"NETTING_GRP_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,28,(defined($a->{"COLLATERAL_GRP_ID_VAL"})? $a->{"COLLATERAL_GRP_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,29,(defined($a->{"PROD_GRP_VAL"})? $a->{"PROD_GRP_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,30,(defined($a->{"HEADER_ID_VAL"})? $a->{"HEADER_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,31,(defined($a->{"DEBT_TENURE_NME_VAL"})? $a->{"DEBT_TENURE_NME_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,32,(defined($a->{"BA_NID_VAL"})? $a->{"BA_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,33,(defined($a->{"REGION_NID_VAL"})? $a->{"REGION_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,34,(defined($a->{"LE_NID_VAL"})? $a->{"LE_NID_VAL"} : '[NULL]'), $format2);


############ FINDING THE COL FORMAT FROM EXCEL SHEET #######################

my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query1.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

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

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

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



my $srl = $sql_worksheet->get_cell($row, 0);
my $cc1 = $sql_worksheet->get_cell($row, 1);
my $vt1 = $sql_worksheet->get_cell($row, 3);
if (not defined $srl or not defined $cc1 or not defined $vt1) {
#    warn "row $row has blanks in one or more of columns 0-2 - skipping";
    next;
}
$srl = $srl->value;
$cc1 = $cc1->value;
$vt1 = $vt1->value;

if (defined $cell and $cell->value eq "$srl") {
   my $cell1 = $sql_worksheet->get_cell($row, 1);

   my $fmt_val = $cell1->value;
   print $fmt_val . "\n";
   my $col_fmt = $a->{"$fmt_val"};

   print "The col format is $col_fmt\n";



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

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Verify_Col_Value1_Test.pl", $posting_event_type, $audit_funding_rule, $col_fmt, $ba_nid, $le_nid, $asset_id, $audit_acct_num, $actual_file2);
  }
}

$row++;



}

$sth->finish();



 
#!/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::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 $posting_event_type = shift;#"PBCLCASHPAY";



#shift;



my $audit_funding_rule = shift;#"PB1";



#shift;

#"PBCLCASHPAY";

#shift;



my $col_fmt = shift;#"CCY";



#shift;

#"INPUT"; 



#shift;



#"INPUT";

my $ba_nid = shift;#10180184;



#shift;

#shift; 

#11082557;

my $le_nid = shift;#20000033;



#shift;

#20000050;

#shift;

#my @c_arr = ($ba_nid, $le_nid);

#11082557;



my $asset_id = shift;

my $audit_acct_num = shift;

#my $sf = shift;

my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";



#shift;



#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";



#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#shift



#my $cc1 = "BA_NID_VAL"; 

 

#shift;

#print "the ba-nid value in Verify Col script is $ba_nid\n"; 



#`cp Temp_Res.xls Temp_Res_bkp.xls`;



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

my $xls_workbook = $xls_parser->Parse("Temp_Res2.xls");

my $xls_worksheet = $xls_workbook->{Worksheet}[0];



my ( $s_row_min, $s_row_max ) = $xls_worksheet->row_range();

my ( $s_col_min, $s_col_max ) = $xls_worksheet->col_range();



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

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



#$format1->set_size(12);

#$format1->set_bold();

##$format1->set_color('red');

#$format1->set_align('center');

#$format1->set_bg_color('red');





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

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

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



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



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



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

#print $cell->value . "\n";





#my $srl = $sql_worksheet->get_cell($row, 0)->value;

#my $cc1 = $sql_worksheet->get_cell($row, 1)->value;

#my $vt1 = $sql_worksheet->get_cell($row, 2)->value;

#my $flag =  $sql_worksheet->get_cell($row, 3)->value;



my $srl = $sql_worksheet->get_cell($row, 0);

my $cc1 = $sql_worksheet->get_cell($row, 1);

my $vt1 = $sql_worksheet->get_cell($row, 3);

if (not defined $srl or not defined $cc1 or not defined $vt1) {

#    warn "row $row has blanks in one or more of columns 0-2 - skipping";

    next;

}

$srl = $srl->value;

$cc1 = $cc1->value;

$vt1 = $vt1->value;

#print "$srl\n";

#print "$cc1\n";

#print "$vt1\n";



 if (defined $cell and $cell->value eq "$srl") {

   my $cell1 = $sql_worksheet->get_cell($row, 1);

#   print "Cell1 value is" .  $cell1->value . "\n";



   if (defined $cell1 and $cell1->value eq "$cc1") {

    

   my $cell3 = $sql_worksheet->get_cell($row, 3);

#   print "Cell3 value is" . $cell3->value . "\n";   



    if (defined $cell3  and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {



############ This is for all values equal to "col_fmt" and flag "Q" ####################################################################



      my $dcol = $sql_worksheet->get_cell($row, 2);

      $dcol = $dcol->value; 

      print "The delta col is $dcol\n";

     

      my $query1 = $sql_worksheet->get_cell($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";

#      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2); 



################## Checkit against same col in delta temp spreadsheet ##############################################



 

     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";



      my $s_row = 0;

      my $s_col = 0;

     for my $s_row($s_row_min+1..$s_row_max) { 

     for my $s_col($s_col_min+1..$s_col_max) {

      my $xls_cell = $xls_worksheet->get_cell($s_row_min, $s_col);

      unless (defined $xls_cell) {next;}

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

#         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;

         $delta_col_val =~ s/^0*//;

         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";



        if ($delta_col_val eq "@data" ) 

         {

          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);

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

          }

        else {

          

#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);

          

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

#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");

           }

         }

      }

     }

    }



      $xls_workbook->SaveAs("Temp_Res2.xls");



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

          }

    elsif (defined $cell3  and $cell3->value ne "NA" and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "H" ) 

         {

############ This is for Value_Type not "NA" but equal to "col_fmt" and flag "H" ######################################################################



          my $query1 = $sql_worksheet->get_cell($row, 6)->value;

          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";

         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);

          

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









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

           }



    elsif (defined $cell3  and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {



########### This is for Value_Type equal to "NA" and flag "Q" ###########################################################

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

       my $query1 = $sql_worksheet->get_cell($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 Second Final Query is --\n\n $query1\n\n";

      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);



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



       }



    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $sql_worksheet->get_cell($row, 4)->value eq "QV" ) {



############# This is for Value_Type equal to "col_fmt" and flag "QV" ###########################################################################

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

       my $query1 = $sql_worksheet->get_cell($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/\$asset_id/$asset_id/;

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

      

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

      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Search_View1_Test.pl", $query1, $actual_file2);



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



       }





    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "H" ) {

    

########### This is for Value_Type equal to "col_fmt" and flag "H" ##############################################################################



    my $query1 = $sql_worksheet->get_cell($row, 6)->value;

          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";

         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);



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









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

           }







    else {

########### This is for all other cases like not equal to "col_fmt", blank rows or unknown flags ###########################################

     next;}

        }

       }

 

  

      }

Open in new window

Temp-Res.xls
Value-Type-Query1.xls
0
Comment
Question by:sunny82
  • 3
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33547757
0
 

Author Comment

by:sunny82
ID: 33548185
Yes, but this is at a later stage of execution of the same program...
0
 

Accepted Solution

by:
sunny82 earned 0 total points
ID: 33573309
Since, I have already got solutions relating to this, I want to close this question.
0
 

Author Comment

by:sunny82
ID: 33589881
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Input Macro 8 22
iPhone excel activation issues 11 65
What is format f12.8 for a CSV file 6 36
Excel Save As Status Box will not go away 6 16
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

937 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

4 Experts available now in Live!

Get 1:1 Help Now