Solved

How do I prevent leading zeroes from not getting truncated while retrieving excel value in Perl

Posted on 2010-08-31
5
427 Views
Last Modified: 2012-06-27
I have a problem. When I am trying to read the AUDIT_ACCT_NUM field from the excel sheet, if the value is "0ABC123",  the $audit_acct_num is retrieved as "0ABC123" which is correct. But if the field has only nos. like "0123", it is getting retrieved as "123" with the leading zero being truncated which is wrong. I tried to make the format of the column as "Text" manually, but still no effect. How can I change the format in the code so that this is solved?

Attached is the code
#!/usr/bin/perl5.10 -w





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_Res3.xls");

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





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

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

   my $xls = "Temp_Res3_Int.xls";

   my $workbook  = Spreadsheet::WriteExcel->new($xls);

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

   

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





   

   for my $col ($col_min .. $col_max) {

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

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





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











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

      $worksheet->write($row,$col,$cell->value);

   }







    $workbook->close();

    system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl", $xls); 

}

Open in new window

0
Comment
Question by:sunny82
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33568738
Maybe this helps:

Add a     '            before your string, this will make Excel recognize it as a string under any circumstances.
0
 

Author Comment

by:sunny82
ID: 33568943
Can you pls tell me where to do this? I think I already tried that..

Here are my 2 codes, one calls the other, the second one prints the values out.

The same problem is still there, where it prints any account no. as 123 which should be "0123" as it is in the excel sheet, but if it is "0XYZ" it is getting printed as "0XYZ"

Code 1 is attached , Code 2 is below ---

=============================================
#!/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 $xls = shift;#"Temp_Res3_Int.xls";
#print $xls;

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

#my $xls_parser   = Spreadsheet::ParseExcel::SaveParser->new();
#my $xls_workbook = $xls_parser->Parse("$xls");
#my $xls_worksheet = $xls_workbook->{Worksheet}[0];




my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("$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);

next unless defined $cell1;

my $cell39 = $sql_worksheet->get_cell($row, 39);

next unless defined $cell39;

my $cell30 = $sql_worksheet->get_cell($row, 30);

next unless defined $cell30;

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

next unless defined $cell6;

my $cell8 = $sql_worksheet->get_cell($row, 8);

next unless defined $cell8;

my $cell16 = $sql_worksheet->get_cell($row, 16);

next unless defined $cell16;

my $cell30val = $cell30->value;

my $posting_event_type =  $cell1->value;
my $audit_funding_rule =  $cell39->value;
my $audit_acct_num  = "$cell30val";
my $ba_nid =  $cell6->value;
my $le_nid =  $cell8->value;
my $asset_id =  $cell16->value;

print "The Posting Event Type is $posting_event_type\n";
print "The Funding Rule is $audit_funding_rule\n";
print "The Acct Num is $audit_acct_num\n";
print "The BA_NID is $ba_nid\n";
print "The LE_NID is $le_nid\n";
print "The Asset Id is $asset_id\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);


}
================================================
#!/usr/bin/perl5.10 -w





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_Res3.xls");

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





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

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

   my $xls = "Temp_Res3_Int.xls";

   my $workbook  = Spreadsheet::WriteExcel->new($xls);

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

   

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





   

   for my $col ($col_min .. $col_max) {

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

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





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











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

      $worksheet->write($row,$col,$cell->value);

   }







    $workbook->close();

    system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl", $xls); 

}

Open in new window

0
 

Author Comment

by:sunny82
ID: 33569920
Any help with this? If I can somehow use write_string method instead of write in Code 1 to write the AUDIT_ACCT_NUM column, it can be done. But it has to be only for that column and not for others as it is entering an unnecessary 0 at the beginning of every value where there is an integer. How do I implement it? Help me pls..
0
 

Accepted Solution

by:
sunny82 earned 0 total points
ID: 33570208
This working...since I know the ACCT_NUM col is 30, I can do this --

if ($col == 30) {
$worksheet->write_string($row,30,$cell->value);
}
else {$worksheet->write($row,$col,$cell->value);}


I am closing the question...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33570254
Ok, sorry I could not be of more help, question turned up in Excel, which together with VB is where Iknow stuff, unfortunately not in Perl :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Recurring Excel Timelime for Veeam 2 31
Need help inserting code to fix a excel bug 8 19
Excel 6 18
onOpen 14 38
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now