List:General Discussion« Previous MessageNext Message »
From:Jason Pruim Date:August 22 2008 9:49am
Subject:Re: Zip Codes with Leading Zeros
View as plain text  
Hi Keith,

I don't know how to fix it in MySQL, but if you import them into  
Excel, select the zip code field, go under format rows under the  
"Special" option is Zip Code. If you use that it will add a leading  
zero to any field that does NOT have at least 5 characters.

Also.. If you have addresses to go with the zip codes, I work at a  
presorted mailing company and we could run your list through our  
software which would check, and verify the address, as well as the zip  
code. It's a very modest charge and we usually have a quick turn  
around. E-mail me off list if you are interested.

Thanks!


On Aug 21, 2008, at 11:07 PM, Keith Spiller wrote:

> Thanks for all your suggestions Tom.  The mixed 5 digit zip code and  
> 10 digit zip+4 code data set are in a varchar(20) field.
>
> I don't recall if the data was identical in both the CSV and Excel  
> files, but I do remember I had the same problem.  It's been many  
> months since I imported the original data into MySQL and I still  
> need to repair the damage zip codes before we attempt another export  
> to Excel.
>
> I believe your final suggestion is my necessary route.  Thanks again  
> for your help Tom.
>
> Keith
>
> ----- Original Message ----- From: "Kralidis,Tom [Burlington]"
> <Tom.Kralidis@stripped 
> >
> To: "Keith Spiller" <larentium@stripped>; <mysql@stripped>
> Sent: Thursday, August 21, 2008 8:28 PM
> Subject: RE: Zip Codes with Leading Zeros
>
>
>>
>> Keith (I'm not very familiar with phpMyAdmin): what is the  
>> underlying datatype of your zip code field?
>>
>> On the command line, if I use:
>>
>> mysql> select * into outfile '/tmp/file.txt' fields terminated by  
>> ',' optionally enclosed by '"' lines terminated by '\n' from tmp;
>>
>> (note that the default output, if not specified is tab-separated)
>>
>> ...I get csv style output in the output file for columns of type  
>> varchar(100), for example, with records containing leading zeros.
>>
>> Are the outputs identical in CSV and Excel (not sure whether Excel  
>> is not showing the leading zeroes as part of the column formatting  
>> defaults).
>>
>> Of course, you could write a post-processing script that adds the  
>> leading zeros to records not long enough.
>>
>> ..Tom
>>
>>
>>
>> -----Original Message-----
>> From: Keith Spiller [mailto:larentium@stripped]
>> Sent: Thu 21-Aug-08 22:11
>> To: mysql@stripped
>> Subject: Zip Codes with Leading Zeros
>>
>> Hi,
>>
>> RE:  Zip Codes with Leading Zeros
>>
>> We need to export a MySQL table with a zip code field to Excel.  We  
>> currently use PhpMyAdmin to export to CSV or Excel files.  We have  
>> had problems with zip codes with leading zeros.  The leading zeros  
>> are removed so that we are left with incomplete codes.  Can you  
>> help us learn the correct procedure for dealing with, exporting and  
>> importing zip codes?
>>
>> Thank you very much for all your help.
>>
>> Keith
>>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
11287 James St
Holland, MI 49424
www.raoset.com
japruim@stripped




Thread
Zip Codes with Leading ZerosKeith Spiller22 Aug
  • RE: Zip Codes with Leading ZerosTom [Burlington] Kralidis22 Aug
    • Re: Zip Codes with Leading ZerosKeith Spiller22 Aug
      • Re: Zip Codes with Leading ZerosAndy Shellam22 Aug
        • RE: Zip Codes with Leading ZerosJerry Schwartz22 Aug
      • Re: Zip Codes with Leading ZerosJason Pruim22 Aug
  • RE: Zip Codes with Leading ZerosJerry Schwartz22 Aug
Re: Zip Codes with Leading ZerosKeith Spiller22 Aug
  • Re: Zip Codes with Leading Zeroswalter harms22 Aug