making leading zero's visible

[LRW]

This may be a question more directed to Excel, but I'm using PHP to
generate a CSV to be imported into Excel, so maybe the solution is
there. So, sorry for crossposting.

Anyway, I'm generating a text file, a CSV, that contains addresses
from a database. Zip codes that have a leading zero (eg: 01234) has
that 0 in the database, and it appears to have it when it's being
inserted and written to the text file, but when you view it in Excel
it goes away.

When I'm creating the string that becomes the CSV file, I surround the
zipcode with doublequotes so it's supposed to go into the file exactly
as written.
Is it an issue with Excel disregarding the quotes and still seeing the
number as purely a number and not a text string?

Is there a way I can alter that at the file generation end, because I
won't have control of the Excel program used when it's outputted.

Thanks for any assistance!
Liam

[Kevin Thorpe]

Anyway, I'm generating a text file, a CSV, that contains addresses
from a database. Zip codes that have a leading zero (eg: 01234) has
that 0 in the database, and it appears to have it when it's being
inserted and written to the text file, but when you view it in Excel
it goes away.

To force Excel to treat entries as text, not numeric prefix the string
with a single quote

[Alan Little]

Carved in mystic runes upon the very living rock, the last words of LRW of
comp.lang.php make plain:

Anyway, I'm generating a text file, a CSV, that contains addresses
from a database. Zip codes that have a leading zero (eg: 01234) has
that 0 in the database, and it appears to have it when it's being
inserted and written to the text file, but when you view it in Excel
it goes away.

When importing, after you specify the delimiter, on the next screen select
all the fields and change the type to Text rather than General. You can
select all the fields at once.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

[Doug Kanter]

"LRW" <de**@celticbea r.com> wrote in message
news:3a******** *************** ***@posting.goo gle.com...

This may be a question more directed to Excel, but I'm using PHP to
generate a CSV to be imported into Excel, so maybe the solution is
there. So, sorry for crossposting.

Anyway, I'm generating a text file, a CSV, that contains addresses
from a database. Zip codes that have a leading zero (eg: 01234) has
that 0 in the database, and it appears to have it when it's being
inserted and written to the text file, but when you view it in Excel
it goes away.

When I'm creating the string that becomes the CSV file, I surround the
zipcode with doublequotes so it's supposed to go into the file exactly
as written.
Is it an issue with Excel disregarding the quotes and still seeing the
number as purely a number and not a text string?

Is there a way I can alter that at the file generation end, because I
won't have control of the Excel program used when it's outputted.

Thanks for any assistance!
Liam

If Kevin's idea works, you should be able to built the single quote into
your text file generator's output. If it doesn't work, come back here and
tell me. I have another solution that's more involved to implement, but
automated once it's been set up and tested.

[Mechphisto]

"Doug Kanter" <an***********@ hotmail.com> wrote in message
news:Cd******** ********@news02 .roc.ny...

If Kevin's idea works, you should be able to built the single quote into
your text file generator's output. If it doesn't work, come back here and
tell me. I have another solution that's more involved to implement, but
automated once it's been set up and tested.

Thanks; the single quote does work.
However, the single quote actually appears in the spreadsheet as well.
I decided on a different workaround: strlen(). If the zipcode is 5 digits
long (because the leading zero is in the data in the database) then
add -0000 to the end.
That makes it a string as well as makes it the same number of digits as the
zipcodes where people included their +4 Zip.

Unless someone has a better method. =)

Thanks all!
Liam

[Doug Kanter]

"Mechphisto " <li**@REEMOVEme chphisto.net> wrote in message
news:u3ioc.7544 1$Ik.5440329@at tbi_s53...

"Doug Kanter" <an***********@ hotmail.com> wrote in message
news:Cd******** ********@news02 .roc.ny...

If Kevin's idea works, you should be able to built the single quote into
your text file generator's output. If it doesn't work, come back here and tell me. I have another solution that's more involved to implement, but
automated once it's been set up and tested.

Thanks; the single quote does work.
However, the single quote actually appears in the spreadsheet as well.
I decided on a different workaround: strlen(). If the zipcode is 5 digits
long (because the leading zero is in the data in the database) then
add -0000 to the end.
That makes it a string as well as makes it the same number of digits as

the zipcodes where people included their +4 Zip.

Unless someone has a better method. =)

Thanks all!
Liam

That's a nice method. And, this is so much better than rec.boats, where
everyone's arguing about politics and anchor chains.

[David McRitchie]

Hi Liam,
There is a zip code format, but I would avoid that because you will not
be able to sort properly on zip code if you do and it makes it good
only for the US. [ mixed US zip and zip+4]

Prior to opening your .csv file change the file extension to .txt so
that the text import wizard will come up and you can tell it which
columns are text -- you want your zip code column to be text.

If you already have your data in the columns you don't need to start
over, I have a macro to fix the US zip codes. The zip+4 are
fine because they are text already, and the macro will ignore them.
Even it they were numbers the macro would ignore numbers with
more than 5 digits. See FixUSzip5 macro in
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#fixuszip5
Format the column as text before running the macro.
Changing the format either way between number and text, has
no effect until the data is reentered, which is what the macro will do.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm