Export number with leading zero to Excel

Bill Campbell bill at celestial.com
Wed Nov 29 15:31:22 PST 2006


On Wed, Nov 29, 2006, Robert T. Repko (R Squared Consultants) wrote:
>I am trying to export data to a comma delimited ASCII file for 
>importing into Excel.  I can export the data to a file but when I 
>open the file in Excel the leading 0 of any number is dropped.  In 
>Excel if I precede a number with an apostrophe (') when I am entering 
>numbers the leading zero appears but not the apostrophe.  If I 
>precede the number with an apostrophe during the export the 
>apostrophe appears with the number in Excel.

The reason that Excel prints the leading zero(s) is that the quote
character makes it treat the entry as a string, not a number.

>How can I keep the leading zero when I open in Excel.

Select an appropriate format for the cell or column that displays
leading zeros.

FWIW:  When I was trying to import data into an OpenOffice.org
spread sheet, I never did get it to handle comma or tab delimited
input files to my liking so I loaded the data to be imported into
a MySQL table, the used OO's data sources to get the data into
the spread sheet.

There are also perl modules, available from CPAN, that allow one
to work with Excel sheets in perl.

Bill
--
INTERNET:   bill at Celestial.COM  Bill Campbell; Celestial Software, LLC
URL: http://www.celestial.com/  PO Box 820; 6641 E. Mercer Way
FAX:            (206) 232-9186  Mercer Island, WA 98040-0820; (206) 236-1676

Suppose you were an idiot.  And suppose you were a member of Congress.  But
I repeat myself.  -- Mark Twain


More information about the Filepro-list mailing list