converting file to excel problem

S

Shawn

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++;
}
 
J

John J. Trammell

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")];
 
J

John McNamara

...
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top