converting file to excel problem

Discussion in 'Perl Misc' started by Shawn, Oct 8, 2003.

  1. Shawn

    Shawn Guest

    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++;
    }
    Shawn, Oct 8, 2003
    #1
    1. Advertising

  2. On Tue, 7 Oct 2003 21:07:22 -0600, 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.


    Do something like:

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

    or use qq for neatness:

    my $starts_with_zero = qq[=("01234")];
    John J. Trammell, Oct 8, 2003
    #2
    1. Advertising

  3. "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
    John McNamara, Oct 8, 2003
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. thomson

    Converting Excel File to pdf

    thomson, Dec 30, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    314
    Curt_C [MVP]
    Dec 30, 2004
  2. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,366
    Carl Prothman [MVP]
    Jan 12, 2005
  3. Anonieko
    Replies:
    2
    Views:
    7,291
    JackStraw73
    Feb 12, 2008
  4. Replies:
    1
    Views:
    1,775
    extentechjohn
    Nov 21, 2006
  5. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,481
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
Loading...

Share This Page