CSV file - Leading Zeros

[Paul Kraus]

Is there a way to write a CSV file so that excel wont drop the leading
zero's from fields? I could use spreadsheet::writeexcel or OLE but
that's like using a sledgehammer for something that needs to be hand
tightened.

Thanks.
Paul

[Rob Dixon]

Paul Kraus wrote:

> Is there a way to write a CSV file so that excel wont drop the leading
> zero's from fields? I could use spreadsheet::writeexcel or OLE but
> that's like using a sledgehammer for something that needs to be hand
> tightened.

Put your numerics in quotes:

"0099"

But, depending on your copy of Excel, you may well not be able to do
arithmetic on those fields. A CSV file simply doesn't hold any
formatting information!

Rob

[Tim Johnson]

I think this is more an Excel question than a Perl question. Excel will
detect that it is a number and show you the equivalent formatted as a
number. I think you can highlight the row and do a Format->Cells and
pick Text as the type. Otherwise if the first character is a single
quote then excel will treat the cell as text.

-----Original Message-----
From: Paul Kraus [mailto:pkraus@pelsupply.com]
Sent: Friday, December 12, 2003 12:49 PM
To: [email]beginners@perl.org[/email]
Subject: CSV file - Leading Zeros

Is there a way to write a CSV file so that excel wont drop the leading
zero's from fields? I could use spreadsheet::writeexcel or OLE but
that's like using a sledgehammer for something that needs to be hand
tightened.

Thanks.
Paul


--
To unsubscribe, e-mail: [email]beginners-unsubscribe@perl.org[/email] For additional
commands, e-mail: [email]beginners-help@perl.org[/email] <http://learn.perl.org/>
<http://learn.perl.org/first-response>

[R. Joseph Newton]

Paul Kraus wrote:

> Is there a way to write a CSV file so that excel wont drop the leading
> zero's from fields? I could use spreadsheet::writeexcel or OLE but
> that's like using a sledgehammer for something that needs to be hand
> tightened.
>
> Thanks.
> Paul

Leading zeroes are meaningful only in strings. If you want leading zeroes
in your data, signify that the value is a string by quoting it. It will
totally make a hash of any numerical functions on the field, but if
leading zeroes are that important...

You could also think about the proper place to concern yourself with data,
and where to concern yourself with it's representation. A database table,
such as a CSV file, has one set of needs. A report has a comletely
different set. Don't wate energy trying to line things up verticall in
stored data. Just translate to your desired presentation format on
extraction.

So why do you need leading zeroes?

Joseph

[Mark Lobue]

At 02:03 PM 12/12/2003, Tim Johnson wrote:

>I think this is more an Excel question than a Perl question. Excel will
>detect that it is a number and show you the equivalent formatted as a
>number. I think you can highlight the row and do a Format->Cells and
>pick Text as the type. Otherwise if the first character is a single
>quote then excel will treat the cell as text.

This doesn't work for me, the single quote is picked up in the conversion process. I don't think it is possible.

-Mark

>-----Original Message-----
>From: Paul Kraus [mailto:pkraus@pelsupply.com]
>Sent: Friday, December 12, 2003 12:49 PM
>To: [email]beginners@perl.org[/email]
>Subject: CSV file - Leading Zeros
>
>Is there a way to write a CSV file so that excel wont drop the leading
>zero's from fields? I could use spreadsheet::writeexcel or OLE but
>that's like using a sledgehammer for something that needs to be hand
>tightened.
>
>Thanks.
>Paul

[Mark Lobue]

At 09:45 PM 12/12/2003, R. Joseph Newton wrote:

>Paul Kraus wrote:
>

>> Is there a way to write a CSV file so that excel wont drop the leading
>> zero's from fields? I could use spreadsheet::writeexcel or OLE but
>> that's like using a sledgehammer for something that needs to be hand
>> tightened.
>>
>> Thanks.
>> Paul

>
>Leading zeroes are meaningful only in strings. If you want leading zeroes
>in your data, signify that the value is a string by quoting it. It will
>totally make a hash of any numerical functions on the field, but if
>leading zeroes are that important...

This doesn't work, Excel still strips the delimiters and displays the number as numeric

>You could also think about the proper place to concern yourself with data,
>and where to concern yourself with it's representation. A database table,
>such as a CSV file, has one set of needs. A report has a comletely
>different set. Don't wate energy trying to line things up verticall in
>stored data. Just translate to your desired presentation format on
>extraction.
>
>So why do you need leading zeroes?

My case is patient medical record numbers, which are always 9 digits and may have a leading zero. I have had this problem a long time, I just format the cells after the conversion process.

-Mark