List:General Discussion« Previous MessageNext Message »
From:Keith Spiller Date:August 22 2008 3:07am
Subject:Re: Zip Codes with Leading Zeros
View as plain text  
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
>
> 


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