sunny82
asked on
Why is this error coming in Perl spreadsheets?
Cell1 value isBA_NID_VAL
Cell3 value isINPUT
The delta col is BA_NID
The First Final Query is --
select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'
The value returned from the db is 10214529
Can't call method "value" on an undefined value at /ms/user/s/sayantag/Verify
at /ms/user/s/sayantag/Verify
main::__ANON__('Can\'t call method "value" on an undefined value at /ms/user/...') called at /ms/user/s/sayantag/Verify
Value-Type-Query1.xls
Cell3 value isINPUT
The delta col is BA_NID
The First Final Query is --
select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'
The value returned from the db is 10214529
Can't call method "value" on an undefined value at /ms/user/s/sayantag/Verify
at /ms/user/s/sayantag/Verify
main::__ANON__('Can\'t call method "value" on an undefined value at /ms/user/...') called at /ms/user/s/sayantag/Verify
#!/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 = "PBTRDPL";#"PBCLCASHPAY";
#shift;
my $audit_funding_rule = "PB36";#shift;#"PB1";
#shift;
#"PBCLCASHPAY";
#shift;
my $col_fmt = "INPUT";#shift;#"CCY";
#shift;
#"INPUT";
#shift;
#"INPUT";
my $ba_nid = 10214529;#shift;#10180184;
#shift;
#shift;
#11082557;
my $le_nid = 20000033;#shift;#20000033;
#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;
my $asset_id = 0;#shift;
my $audit_acct_num = '083920660';#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_Res3_Int.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;
next unless defined $delta_col_val;
$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;}
}
}
}
#!/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 = "PBTRDPL";#"PBCLCASHPAY";
#shift;
my $audit_funding_rule = "PB36";#shift;#"PB1";
#shift;
#"PBCLCASHPAY";
#shift;
my $col_fmt = "INPUT";#shift;#"CCY";
#shift;
#"INPUT";
#shift;
#"INPUT";
my $ba_nid = 10214529;#shift;#10180184;
#shift;
#shift;
#11082557;
my $le_nid = 20000033;#shift;#20000033;
#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;
my $asset_id = 0;#shift;
my $audit_acct_num = '083920660';#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_Res3_Int.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;
next unless defined $delta_col_val;
$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;}
}
}
}
Value-Type-Query1.xls
ozo
$xls_worksheet->get_cell($
ASKER
There are a couple of blank lines in the Temp-Res3_Init.xls sheet. I am matching the column header from Value_Type_Query1.xls sheet with the first row which contains col headers. If it matches it should go to the corresponding value in that column.
I did
my $delta_col_val = $xls_worksheet->get_cell($
next unless defined $delta_col_val;
to take care of blank lines, but still no help. Pls help.
I did
my $delta_col_val = $xls_worksheet->get_cell($
next unless defined $delta_col_val;
to take care of blank lines, but still no help. Pls help.
try
next unless defined $xls_worksheet->get_cell($
my $delta_col_val = $xls_worksheet->get_cell($
next unless defined $xls_worksheet->get_cell($
my $delta_col_val = $xls_worksheet->get_cell($
ASKER
Trying the above, I am getting
Cell1 value isBA_NID_VAL
Cell3 value isINPUT
The dcol is BA_NID
The First Final Query is --
select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'
The value returned from the db is 10214529
Cell1 value isBA_NID_VAL
Cell3 value isBACPMOTCCOLL
It does not contain any errors but its not being able to print $delta_col_val. After the header is read if you look into my excel sheet Temp_Res3_Init.xls, there are a couple of blank rows then the BA_NID value is there. It is not being able to read it.
here is what i did --
==========================
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($
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
# print "The column name is " . $xls_worksheet->get_cell($
# print "The Delta Col value is " . $xls_worksheet->get_cell($
# print $s_row;
# print $s_col;
# my $delta_col_val = $xls_worksheet->get_cell($
next unless defined my $delta_col_val;
$delta_col_val = $xls_worksheet->get_cell($
$delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $xls_worksheet->get_cell($
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
}
}
}
$xls_workbook->SaveAs("Tem
==========================
Cell1 value isBA_NID_VAL
Cell3 value isINPUT
The dcol is BA_NID
The First Final Query is --
select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'
The value returned from the db is 10214529
Cell1 value isBA_NID_VAL
Cell3 value isBACPMOTCCOLL
It does not contain any errors but its not being able to print $delta_col_val. After the header is read if you look into my excel sheet Temp_Res3_Init.xls, there are a couple of blank rows then the BA_NID value is there. It is not being able to read it.
here is what i did --
==========================
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($
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
# print "The column name is " . $xls_worksheet->get_cell($
# print "The Delta Col value is " . $xls_worksheet->get_cell($
# print $s_row;
# print $s_col;
# my $delta_col_val = $xls_worksheet->get_cell($
next unless defined my $delta_col_val;
$delta_col_val = $xls_worksheet->get_cell($
$delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $xls_worksheet->get_cell($
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
}
}
}
$xls_workbook->SaveAs("Tem
==========================
ASKER
Any solutions?
Trying this also, same result, $delta_col_val not getting printed probably because it is a blank, so have to find a way to skip those blank rows, and take the first non blank value in the same column..
==========================
for my $s_row($s_row_min .. $s_row_max) {
for my $s_col($s_col_min+1 .. $s_col_max) {
my $xls_cell = $xls_worksheet->get_cell($
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
# print "The column name is " . $xls_worksheet->get_cell($
# print "The Delta Col value is " . $xls_worksheet->get_cell($
# print $s_row;
# print $s_col;
# my $delta_col_val = $xls_worksheet->get_cell($
next unless defined my $delta_col_val;
$delta_col_val = $xls_worksheet->get_cell($
$delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $xls_worksheet->get_cell($
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
}
}
}
$xls_workbook->SaveAs("Tem
==========================
Trying this also, same result, $delta_col_val not getting printed probably because it is a blank, so have to find a way to skip those blank rows, and take the first non blank value in the same column..
==========================
for my $s_row($s_row_min .. $s_row_max) {
for my $s_col($s_col_min+1 .. $s_col_max) {
my $xls_cell = $xls_worksheet->get_cell($
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
# print "The column name is " . $xls_worksheet->get_cell($
# print "The Delta Col value is " . $xls_worksheet->get_cell($
# print $s_row;
# print $s_col;
# my $delta_col_val = $xls_worksheet->get_cell($
next unless defined my $delta_col_val;
$delta_col_val = $xls_worksheet->get_cell($
$delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $xls_worksheet->get_cell($
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
}
}
}
$xls_workbook->SaveAs("Tem
==========================
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your solution worked. I also tried an alternate solution by changing the loop structure and it worked fine too.
I did this
==========================
or my $s_col($s_col_min+1 .. $s_col_max) {
my $xls_cell = $xls_worksheet->get_cell(0
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
for my $s_row($s_row_min+1 .. $s_row_max) {
my $delta_col_val = $xls_worksheet->get_cell($
if (defined $delta_col_val) {
$delta_col_val = $delta_col_val->value;
# $delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $delta_col_val . "\n";
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
else {next;}
}
}
else {next;}
}
}
==========================
I did this
==========================
or my $s_col($s_col_min+1 .. $s_col_max) {
my $xls_cell = $xls_worksheet->get_cell(0
next unless defined $xls_cell;
if (defined $xls_cell and $xls_cell->value eq "$dcol") {
for my $s_row($s_row_min+1 .. $s_row_max) {
my $delta_col_val = $xls_worksheet->get_cell($
if (defined $delta_col_val) {
$delta_col_val = $delta_col_val->value;
# $delta_col_val =~ s/^0*//;
print "The Delta Col value is " . $delta_col_val . "\n";
if ($delta_col_val eq "@data" )
{
# $xls_worksheet->AddCell($s
print "\n\nTEST PASSED\n\n";
}
else {
$xls_worksheet->AddCell($s
print "\n\nTEST FAILED\n\n";
# $xls_workbook->SaveAs("Tem
}
}
else {next;}
}
}
else {next;}
}
}
==========================
ASKER
Both the solutions worked...Thx..