making leading zero's visible

L

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
 
K

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.
Click to expand...

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

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.
Click to expand...

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

Doug Kanter

LRW said:
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
Click to expand...

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

Mechphisto

Doug Kanter said:
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.
Click to expand...

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
 
D

Doug Kanter

Mechphisto said:
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
Click to expand...

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

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

yanseiler

In your csv file, use =T("0234") instead of "0234"

This will tell Excel that this is a text field and the leading zer
won't be stripped out

Cheer
 
F

Fungo

I struggled with this exact problem and came up with a nice solution.
It turns out just including a space before the zip code doesn't help
but including the unicode escape character for a space before the zi
code works like a charm. In your code just make sure your csv outpu
for the zip code looks like this:

\u00A0xxxxx where xxxxx is the zip code

This will do the trick, but keep in mind there will now be a "space
before the zip code in the excel spreadsheet...which shouldn't be
problem in most cases. The previous person's solution of usin
=T("xxxxx") was also a good solution, but I didn't want my users to se
the actual =T("xxxxx") in the fx field or when they try to edit it. S
this escape code for a nbsp (non-breaking space) does the trick an
it's the least invasive in terms of what the users see.

Good luc
 
D

David McRitchie

Might look good but wouldn't work with Mail Merge (to get postal
bar coding) and you'd have to be real consistent to get it to "work"
for other things.

Ok I tried that in cell, now how do you get it to actually work.

It took me a long time to discover VBA CHRw function
http://www.mvps.org/dmcritchie/rexx/htm/unicode.htm

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
 
Top