Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > converting file to excel problem

Reply
Thread Tools

converting file to excel problem

 
 
Shawn
Guest
Posts: n/a
 
      10-08-2003
Hi,

We are using the below script to convert a file into excel format. The
problem is that my file contains ssn in which they can start with zero.
Well, when it gets converted to excel it drops the leading zero. I need
that leading zero and am not sure how to modify this script to keep the
zero.

Any asistance would be greatly appreciated!

Shawn

--


#!/opt/bin/perl5.6 -w
################################################## ##########################
###
# Example of how to use the WriteExcel module
# Program to convert a text [delim] separated value file into an Excel file.
# Usage: txt2xls.pl file.txt newfile.xls

use Getopt::Long;
use Spreadsheet::WriteExcel::Big;

GetOptions ("d=s" => \$delim);
$delim = "|" if !defined($delim);
$delim =~ s/\|/\\|/g;

# Check for valid number of arguments
if (($#ARGV < 1) || ($#ARGV > 2)) {
die("Usage: txt2xls file.txt newfile.xls\n");
};

# Open the Comma Seperated Variable file
open (TXTFILE, $ARGV[0]) or die "$ARGV[0]: $!";

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel::Big->new($ARGV[1]);
my $worksheet = $workbook->add_worksheet();


# Row and column are zero indexed
my $row = 0;

while (<TXTFILE>) {
chomp;
@cols = split(/\s*${delim}\s*/,$_);

$col = 0;
foreach my $token (@cols) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}


 
Reply With Quote
 
 
 
 
John J. Trammell
Guest
Posts: n/a
 
      10-08-2003
On Tue, 7 Oct 2003 21:07:22 -0600, Shawn <(E-Mail Removed)> wrote:
> We are using the below script to convert a file into excel format. The
> problem is that my file contains ssn in which they can start with zero.
> Well, when it gets converted to excel it drops the leading zero. I need
> that leading zero and am not sure how to modify this script to keep the
> zero.


Do something like:

my $starts_with_zero = "=(\"01234\")";

or use qq for neatness:

my $starts_with_zero = qq[=("01234")];

 
Reply With Quote
 
 
 
 
John McNamara
Guest
Posts: n/a
 
      10-08-2003
"Shawn" wrote ...

> We are using the below script to convert a file into excel format. The
> problem is that my file contains ssn in which they can start with zero.
> Well, when it gets converted to excel it drops the leading zero. I need
> that leading zero and am not sure how to modify this script to keep the
> zero.


You can fix this by calling the keep_leading_zeros() method for the
worksheet.

...

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

$worksheet->keep_leading_zeros();

...


See the "keep_leading_zeros" section of the Spreadsheet::WriteExcel
documentation for a full explanation.

John.
--
perl -MCPAN -e 'install jmcnamara & _ x ord $ ;' | tail -1
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to return an excel file or excel data from ASP.NET Anonieko ASP .Net 2 02-12-2008 07:03 PM
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
ExtenXLS loads data into Excel 2002 file but damages the Excel file. kp2900@gmail.com Java 1 11-21-2006 05:48 PM
exporting an excel file from database; making changes to excel file and updating the database by importing it back Luis Esteban Valencia ASP .Net 1 01-12-2005 12:28 AM
Converting Excel File to pdf thomson ASP .Net 1 12-30-2004 04:31 AM



Advertisments