sunny82
asked on
How do I solve this Perl Spreadsheet problem
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::S aveParser;
use Spreadsheet::WriteExcel::B ig;
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->n ew();
my $sql_workbook = $sql_parser->parse("Temp_R es.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/a utomation_ framework/ TgtFile/de lta_LibraP rimeBroker age.txt";
print "$audit_acct_num\n";
system("/ms/dist/perl5/bin /perl5.10" , "/ms/user/s/sayantag/Acces s_ETL_Rule s1_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::S aveParser;
use Spreadsheet::WriteExcel::B ig;
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/phoeni xfunding/e tl/data/qa /TgtFiles/ delta_Libr aPrimeBrok erage.txt" ;
#"/v/region/na/appl/qatool s/util/dat a/reports/ SF/OtcTaxl otOpenDelt a.txt";
#shift;
my $workbook  = Spreadsheet::WriteExcel::B ig->new("E TL_Rules_R es.xls");
my $worksheet = $workbook->addworksheet("R esults");
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('cente r');
$format1->set_bg_color('ye llow');
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_VA L"
,"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_VA L"
,"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:NQ 230001', '', '', { '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_RUL E_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_EVE NT_TYPE_CD E"})? $a->{"POSTING_EVENT_TYPE_C DE"} : '[NULL]'), $format2);
$worksheet->write($row,3, (defined($a->{"CHANGE_CATE GORY_ID_VA L"})? $a->{"CHANGE_CATEGORY_ID_V AL"} : '[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_I D_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_CC Y_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_C NTRPRTY_ID _VAL"})? $a->{"EXT_CNTRPRTY_ID_VAL" } : '[NULL]'), $format2);
$worksheet->write($row,12, (defined($ a->{"BA_CN TRPRTY_ID_ VAL"})? $a->{"BA_CNTRPRTY_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,13, (defined($ a->{"INTER CPNY_CNTRP RTY_ID_VAL "})? $a->{"INTERCPNY_CNTRPRTY_I D_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,14, (defined($ a->{"REGIO N_CNTRPRTY _ID_VAL"}) ? $a->{"REGION_CNTRPRTY_ID_V AL"} : Â '[NULL]'), $format2);
$worksheet->write($row,15, (defined($ a->{"CLIEN T_ID_VAL"} )? $a->{"CLIENT_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,16, (defined($ a->{"UOM_C CY_VAL"})? $a->{"UOM_CCY_VAL"} : '[NULL]'), $format3);
$worksheet->write($row,17, (defined($ a->{"PRIMA RY_FCP_ID" })? $a->{"PRIMARY_FCP_ID"} : '[NULL]'), $format2);
$worksheet->write($row,18, (defined($ a->{"MULTI PLIER_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_T ME"})? $a->{"LAST_UPDT_DTE_TME"} : '[NULL]'), $format2);
$worksheet->write($row,22, (defined($ a->{"CLEAR ING_HOUSE_ CDE_VAL"}) ? $a->{"CLEARING_HOUSE_CDE_V AL"} : '[NULL]'), $format2);
$worksheet->write($row,23, (defined($ a->{"SEC_P OOL_ID_VAL "})? $a->{"SEC_POOL_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,24, (defined($ a->{"TICKE T_REF_NO_V AL"})? $a->{"TICKET_REF_NO_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,25, (defined($ a->{"MASTE R_TICKET_R EF_NO_VAL" })? $a->{"MASTER_TICKET_REF_NO _VAL"} : '[NULL]'), $format2);
$worksheet->write($row,26, (defined($ a->{"LEG_N ME_VAL"})? $a->{"LEG_NME_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,27, (defined($ a->{"NETTI NG_GRP_ID_ VAL"})? $a->{"NETTING_GRP_ID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,28, (defined($ a->{"COLLA TERAL_GRP_ ID_VAL"})? $a->{"COLLATERAL_GRP_ID_VA L"} : '[NULL]'), $format2);
$worksheet->write($row,29, (defined($ a->{"PROD_ GRP_VAL"}) ? $a->{"PROD_GRP_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,30, (defined($ a->{"HEADE R_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_NI D_VAL"})? $a->{"BA_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,33, (defined($ a->{"REGIO N_NID_VAL" })? $a->{"REGION_NID_VAL"} : '[NULL]'), $format2);
$worksheet->write($row,34, (defined($ a->{"LE_NI D_VAL"})? $a->{"LE_NID_VAL"} : '[NULL]'), $format2);
############ FINDING THE COL FORMAT FROM EXCEL SHEET #######################
my $sql_parser  = Spreadsheet::ParseExcel->n ew();
my $sql_workbook = $sql_parser->parse("Value_ Type_Query 1.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/Verif y_Col_Valu e1_Test.pl ", $posting_event_type, $audit_funding_rule, $col_fmt, $ba_nid, $le_nid, $asset_id, $audit_acct_num, $actual_file2);
 }
}
$row++;
}
$sth->finish();
Â
Value-Type-Query1.xls
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
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::S
use Spreadsheet::WriteExcel::B
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->n
my $sql_workbook = $sql_parser->parse("Temp_R
my $sql_worksheet = Â $sql_workbook->worksheet("
my ( $row_min, $row_max ) = $sql_worksheet->row_range(
for my $row ( $row_min+1 .. $row_max ) {
my $cell1 = $sql_worksheet->get_cell($
my $cell39 = $sql_worksheet->get_cell($
my $cell30 = $sql_worksheet->get_cell($
my $cell6 = $sql_worksheet->get_cell($
my $cell8 = $sql_worksheet->get_cell($
my $cell16 = $sql_worksheet->get_cell($
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($
my $audit_funding_rule = Â $sql_worksheet->get_cell($
my $audit_acct_num  = $sql_worksheet->get_cell($
my $ba_nid = Â $sql_worksheet->get_cell($
my $le_nid = Â $sql_worksheet->get_cell($
my $asset_id = Â $sql_worksheet->get_cell($
my $actual_file2 = "/v/region/na/appl/qatools
print "$audit_acct_num\n";
system("/ms/dist/perl5/bin
}
}
==========================
==========================
Code 2
==========================
#!/ms/dist/perl5/bin/perl5
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::S
use Spreadsheet::WriteExcel::B
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/phoeni
#"/v/region/na/appl/qatool
#shift;
my $workbook  = Spreadsheet::WriteExcel::B
my $worksheet = $workbook->addworksheet("R
my $format1 = $workbook->add_format();
my $format2 = $workbook->add_format();
my $format3 = $workbook->add_format(num_
$format1->set_size(12);
$format1->set_bold();
$format1->set_color('blue'
$format1->set_align('cente
$format1->set_bg_color('ye
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_VA
,"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_VA
,"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:NQ
     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_RUL
$worksheet->write($row,1, (defined($a->{"INPUT_VALUE
$worksheet->write($row,2, (defined($a->{"POSTING_EVE
$worksheet->write($row,3, (defined($a->{"CHANGE_CATE
$worksheet->write($row,4, (defined($a->{"AQUISITION_
$worksheet->write($row,5, (defined($a->{"AQUISITION_
$worksheet->write($row,6, (defined($a->{"DUE_DTE_VAL
$worksheet->write($row,7, (defined($a->{"DUE_DTE_LPT
$worksheet->write($row,8, (defined($a->{"CCY_VAL"})?
$worksheet->write($row,9, (defined($a->{"TRNSLTED_CC
$worksheet->write($row,10,
$worksheet->write($row,11,
$worksheet->write($row,12,
$worksheet->write($row,13,
$worksheet->write($row,14,
$worksheet->write($row,15,
$worksheet->write($row,16,
$worksheet->write($row,17,
$worksheet->write($row,18,
$worksheet->write($row,19,
$worksheet->write($row,20,
$worksheet->write($row,21,
$worksheet->write($row,22,
$worksheet->write($row,23,
$worksheet->write($row,24,
$worksheet->write($row,25,
$worksheet->write($row,26,
$worksheet->write($row,27,
$worksheet->write($row,28,
$worksheet->write($row,29,
$worksheet->write($row,30,
$worksheet->write($row,31,
$worksheet->write($row,32,
$worksheet->write($row,33,
$worksheet->write($row,34,
############ FINDING THE COL FORMAT FROM EXCEL SHEET #######################
my $sql_parser  = Spreadsheet::ParseExcel->n
my $sql_workbook = $sql_parser->parse("Value_
my $sql_worksheet = $sql_workbook->worksheet("
my ( $row_min, $row_max ) = $sql_worksheet->row_range(
for my $row ( $row_min+1 .. $row_max ) {
my $cell = Â $sql_worksheet->get_cell($
my $srl = $sql_worksheet->get_cell($
my $cc1 = $sql_worksheet->get_cell($
my $vt1 = $sql_worksheet->get_cell($
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($
  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
 }
}
$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;}
}
}
}
Temp-Res.xlsValue-Type-Query1.xls
ASKER
Yes, but this is at a later stage of execution of the same program...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q__26432276.html
?