Solved

How do I solve this Perl Spreadsheet problem

Posted on 2010-08-26
14
1,256 Views
Last Modified: 2013-11-19
I have a flat file. I want to process the first row of the flat file, put it into a temporary excel sheet Temp_Res.xls, do some validations with its column value with the data returned from the database, mark the column as red for failed and then move it into the final Excel sheet called Results.xls and then move onto the next row of the flat file and proceed in the same way. So at any point of time the temporary excel sheet should just have 1 row which will be over-written everytime and all the rows will then show in the final excel sheet Results.xls

The validations are done from another excel sheet Value_Type_Query1.xls based on some predefined input value types.

I have 3 codes here. The problem is after processing the first row from flat file and putting it into the temporary excel sheet, I cannot overwrite the same excel sheet for data validations and coloring etc without processing the other rows. It says undefined value error. But if I copy the same Temp_Res excel created earlier into another excel and then try to do validations on it, it is working fine.

Where is the problem with the code? Pls help

Attached is the code 1. I am also putting Code 2 and Code 3 below. So In Code 3 if I replace "Temp_Res.xls" with say like "Temp_Res_bkp.xls" which is just a copy of "Temp_Res.xls", it is working fine. But keeping it as it is, throws me an error in Code 3 saying that "Can't call method "col_range" on an undefined value at /ms/user/s/sayantag/Verify_Col_Value1_Test.pl line 87."

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();
==========================================================




Code 3:
============================================================
#!/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_Res.xls");
my $xls_worksheet = $xls_workbook->{Worksheet}[0];

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

my $format1 = $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_col($s_col_min+1..$s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell(0, $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(1, $s_col)->value . "\n";
         my $delta_col_val = $xls_worksheet->get_cell(1, $s_col)->value;
         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $xls_worksheet->get_cell(1, $s_col)->value . "\n";

        if ($delta_col_val eq "@data") {
          print "\n\nTEST PASSED\n\n";
          }
        else {
         
          $xls_worksheet->AddCell(1, $s_col, $delta_col_val, $format1);
          print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
         }
      }
    }
      $xls_workbook->SaveAs("Temp_Res.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 warnings;

use Spreadsheet::WriteExcel::Big;

use Spreadsheet::ParseExcel;

use strict;

use IO::Scalar;

use Data::Dumper;



my $sf;

my $actual_file1;

my $actual_file2;

my $generated_file;

my $acf;

my $gen;

my $acf_xls;

my $gen_xls;





#my $xls_dir = shift;

#my $CFP = shift;

#print $xls_dir;



if (! -d "New") {

`mkdir New`;

}

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



#print "\n\nWhat is your Source File Name? \n\n";

$sf = shift;

#shift;

#<STDIN>;

chomp $sf;

$sf = "/v/region/na/appl/qatools/util/data/reports/SF/" . $sf;

print "\n\nThe Source File Name is $sf \n\n";





#print "\n\nWhat is your Delta File Name? \n\n";

$actual_file1 = shift;

#shift;

#<STDIN>;

chomp $actual_file1;



my $dir = "/v/region/na/appl/qatools/util/data/reports/automation_framework/TgtFile";

#chdir($dir);



$actual_file2 = "$dir/$actual_file1";

#print $actual_file2 . "\n";

print "\n\nThe Actual File Name is $actual_file2 \n\n";





$acf = $actual_file1;

$acf =~ s/\.txt//;

#print $acf . "\n";

#print "\n";







$acf_xls =  "New/" . $acf . ".xls";



my $workbook  = Spreadsheet::WriteExcel::Big->new("Temp_Res.xls");

my $worksheet = $workbook->addworksheet();









my @XLSarray;

#my $data_row;

my $row = 0;

my $col=1;



#print $col;

#print $row;



my $format1 = $workbook->add_format();

my $format2 = $workbook->add_format();









$format1->set_size(12);

$format1->set_bold();

$format1->set_color('blue');

$format1->set_align('center');

$format1->set_bg_color('yellow');







$format2->set_num_format ('000000000');





#print "The column value is $col\n";

#print "The row value is $row\n";



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

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



############### First File #######################



############ Write the column names #############

$worksheet->write(0, 1, "POSTING_EVENT_TYPE", $format1);

$worksheet->write(0, 2, "LEDGER_TYPE", $format1);

$worksheet->write(0, 3, "ACCTNG_PERIOD_ID", $format1);

$worksheet->write(0, 4, "HRCHY_EFFECTIVE_DTE", $format1);

$worksheet->write(0, 5, "BA_HRCHY_ID", $format1);

$worksheet->write(0, 6, "BA_NID", $format1);

$worksheet->write(0, 7, "LE_HRCHY_ID", $format1);

$worksheet->write(0, 8, "LE_NID", $format1);

$worksheet->write(0, 9, "REGION_HRCHY_ID", $format1);

$worksheet->write(0, 10, "REGION_NID", $format1);

$worksheet->write(0, 11, "CHANGE_CTGRY_ID", $format1);

$worksheet->write(0, 12, "ELIMINATION_ID", $format1);

$worksheet->write(0, 13, "SRC_ID", $format1);

$worksheet->write(0, 14, "VALUE", $format1);

$worksheet->write(0, 15, "RUN_GROUP_ID", $format1);

$worksheet->write(0, 16, "ASSET_ID", $format1);

$worksheet->write(0, 17, "EXTERNAL_CNTRPTY_ID", $format1);

$worksheet->write(0, 18, "REGION_CNTRPTY_ID", $format1);

$worksheet->write(0, 19, "BA_CNTRPTY_ID", $format1);

$worksheet->write(0, 20, "AQUISITION_DTE", $format1);

$worksheet->write(0, 21, "AQUISITION_DTE_LPT_ID", $format1);

$worksheet->write(0, 22, "DUE_DTE", $format1);

$worksheet->write(0, 23, "DUE_DTE_LPT_ID", $format1);

$worksheet->write(0, 24, "CLIENT_ID", $format1);

$worksheet->write(0, 25, "INTERCPNY_CNTRPTY_ID", $format1);

$worksheet->write(0, 26, "DEBT_TENURE_NME", $format1);

$worksheet->write(0, 27, "CCY", $format1);

$worksheet->write(0, 28, "TRANSLATED_CCY", $format1);

$worksheet->write(0, 29, "UOM_CCY", $format1);

$worksheet->write(0, 30, "AUDIT_ACCT_NUM", $format1);

$worksheet->write(0, 31, "QTP_KEY_ID", $format1);

$worksheet->write(0, 32, "AUDIT_CPNY_CDE", $format1);

$worksheet->write(0, 33, "AUDIT_COST_CNTR_NME", $format1);

$worksheet->write(0, 34, "AUDIT_JRNL_XML", $format1);

$worksheet->write(0, 35, "AUDIT_ACCOUNT_RULE", $format1);

$worksheet->write(0, 36, "AUDIT_PRODUCT_RULE", $format1);

$worksheet->write(0, 37, "AUDIT_SOURCE", $format1);

$worksheet->write(0, 38, "AUDIT_TD_SD_PEND", $format1);

$worksheet->write(0, 39, "AUDIT_FUNDING_RULE", $format1);

$worksheet->write(0, 40, "CLEARING_HOUSE_CDE", $format1);

$worksheet->write(0, 41, "SEC_POOL_ID", $format1);

$worksheet->write(0, 42, "TICKET_REF_NO", $format1);

$worksheet->write(0, 43, "MASTER_TICKET_REF_NO", $format1);

$worksheet->write(0, 44, "LEG_TYPE_DSC", $format1);

$worksheet->write(0, 45, "NETTING_GRP_ID", $format1);

$worksheet->write(0, 46, "COLLATERAL_GRP_ID", $format1);

$worksheet->write(0, 47, "PROD_GRP_DSC", $format1);

$worksheet->write(0, 48, "HEADER_ID", $format1);



$row +=1;



open ("ACT", $actual_file2) || die "Cannot open $actual_file2\n";



while (my $data_row = <ACT>) {





my (

 $posting_event_type

,$ledger_type

,$acctng_period_id

,$hrchy_effective_dte

,$ba_hrchy_id

,$ba_nid

,$le_hrchy_id

,$le_nid

,$region_hrchy_id

,$region_nid

,$change_ctgry_id

,$elimination_id

,$src_id

,$value

,$run_group_id

,$asset_id

,$external_cntrpty_id

,$region_cntrpty_id

,$ba_cntrpty_id

,$aquisition_dte

,$aquisition_dte_lpt_id

,$due_dte

,$due_dte_lpt_id

,$client_id

,$intercpny_cntrpty_id

,$debt_tenure_nme

,$ccy

,$translated_ccy

,$uom_ccy

,$audit_acct_num

,$qtp_key_id

,$audit_cpny_cde

,$audit_cost_cntr_nme

,$audit_jrnl_xml

,$audit_account_rule

,$audit_product_rule

,$audit_source

,$audit_td_sd_pend

,$audit_funding_rule

,$clearing_house_cde

,$sec_pool_id

,$ticket_ref_no

,$master_ticket_ref_no

,$leg_type_dsc

,$netting_grp_id

,$collateral_grp_id

,$prod_grp_dsc

,$header_id

) = split (/\|/, $data_row);









push @XLSarray,

($posting_event_type

,$ledger_type

,$acctng_period_id

,$hrchy_effective_dte

,$ba_hrchy_id

,$ba_nid

,$le_hrchy_id

,$le_nid

,$region_hrchy_id

,$region_nid

,$change_ctgry_id

,$elimination_id

,$src_id

,$value

,$run_group_id

,$asset_id

,$external_cntrpty_id

,$region_cntrpty_id

,$ba_cntrpty_id

,$aquisition_dte

,$aquisition_dte_lpt_id

,$due_dte

,$due_dte_lpt_id

,$client_id

,$intercpny_cntrpty_id

,$debt_tenure_nme

,$ccy

,$translated_ccy

,$uom_ccy

,$audit_acct_num

,$qtp_key_id

,$audit_cpny_cde

,$audit_cost_cntr_nme

,$audit_jrnl_xml

,$audit_account_rule

,$audit_product_rule

,$audit_source

,$audit_td_sd_pend

,$audit_funding_rule

,$clearing_house_cde

,$sec_pool_id

,$ticket_ref_no

,$master_ticket_ref_no

,$leg_type_dsc

,$netting_grp_id

,$collateral_grp_id

,$prod_grp_dsc

,$header_id

);



#print "The column value is $col\n";

#print "The row value is $row\n";



$col = 1;



foreach my $XLSelement (@XLSarray) {

$worksheet->write_string($row, $col, $XLSelement, $format2);

$col++;

}

#print "$posting_event_type\n";

`mv Temp_Res.xls Temp_Res_bkp.xls`;

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



@XLSarray = ();

#`cp Temp_Res.xls Temp_Res_bkp.xls`; 

#$workbook->close;

#exit(1); 

$row++;

}

close("ACT");

############# END FIRST FILE ###########################################

Open in new window

Value-Type-Query1.xls
0
Comment
Question by:sunny82
  • 7
  • 4
14 Comments
 
LVL 10

Expert Comment

by:jeromee
Comment Utility
Sunny,
I failed to understand why you need the temporary Temp_Res.xls and why you don't do the validation "in memory" before putting the data into the final xls file?
0
 

Author Comment

by:sunny82
Comment Utility
I wanted the Temp_Res.xls file because I was intially putting the data into a spreadsheet from the flat file, so that I can compare col by col in the Temp_Res.xls spreadsheet and then display it in the Results spreadsheet. For this approach, what do you think will be the change in the code? Pls remember it is pipe delimited flat file with no col names and the query from the Value Type Query spreadsheet returns a col value.

Otherwise how to do the validation of a flat file col by col from memory? Here I am mentioning the cols to be compared in the Delta_Col column in Value_Type_Query spreadsheet by mapping it against ETL rules table columns which are again outputted by Code 2 . Then the actual comparison is done by Code 3 by mapping the ETL rules column with Delta Columns, checking if the Input Type matches with ETL Rules output and then processing the corresponding query from the excel sheet and finally matching it with the every row stored in Temp_Res.xls spreadsheet.
0
 

Author Comment

by:sunny82
Comment Utility
What do you think I can do to fix this problem? Pls let me know your approach also.

Anyone of you pls help...
0
 

Author Comment

by:sunny82
Comment Utility
Its just that I have to modify Code 1 in someway like adding $workbook->close or something( I even tried that but did not work) that after reading every row one at a time from the flat file and putting it into Temp_Res.xls file, I can modify that same file for validation in Code 3 using Parseexcel Saveparser module. Code 3 (The validation part) is working fine,  just that I cannot write 1 row at a time into Temp_Res.xls and use that same file for validation in Code 3.
0
 

Author Comment

by:sunny82
Comment Utility
Even if you think that removing the intermediate Temp_Res.xls will also be fine and the whole verification can be done in Results sheet. Whichever you think is the best way. Pls help me what changes need to be made here to achieve the output.
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
sunny82,

Why are you using Perl to read a pipe-delimited text file into Excel when that can be done using Excel's native VBA? As has been mentioned already the comparisons can all be carried in the code without writing anything to screen, other than the result - all using VBA. It seems to me that you are using a sledge hammer to crack a nut here.

If you have a fixed number of columns for each row of data there is no need to have column headers/names - provided there are the requisite number of pipe characters for each and every row of data.

Please upload the pipe-delimited file as well as an Excel file illustrating what you want done - both as small a file as you can to illustrate what needs doing.

Patrick
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:sunny82
Comment Utility
Pls give me a few days, I will definitely post what I need...For now, I am working on an alternate way which is a workaround.
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
Comment Utility
Sure thing. BTW the VBA code to do what you want - read a text file into Excel - is very short by comparison to what you have posted in this question.
0
 

Author Comment

by:sunny82
Comment Utility
I do not know VBA that much...If possible, you can send me the  code..
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
The other way round. As you know little VBA, you upload your small sample files and I'll see if I can get it to work.
0
 

Author Comment

by:sunny82
Comment Utility
Thx Patrick, I have solved the problem with perl at

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26442490.html

Thanks for your help.
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
sunny82,

Pleased to hear you've solved the problem with Perl. I suggest you accept one of your own comments as the answer/solution - so closing this question without awarding points.

Patrick
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

15 Experts available now in Live!

Get 1:1 Help Now