List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:August 22 2008 1:38pm
Subject:RE: Zip Codes with Leading Zeros
View as plain text  
>-----Original Message-----
>From: Andy Shellam [mailto:andy-lists@stripped]
>Sent: Friday, August 22, 2008 4:39 AM
>To: mysql@stripped
>Subject: Re: Zip Codes with Leading Zeros
>
>FYI, we have the same issue with exporting phone numbers from MS SQL.
>All phones numbers
>in the UK start with zero which causes no end of grief when exporting
>data to Excel.
>
>Even if we then change the column data type to "text", it doesn't put
>the zeros back in
>(it's like Excel has deleted them.)  We have to put an extra format on
>the field in Excel
>of "00000000000" (11 zeros) which says that the field should be 11
>characters long with
>zeros where there isn't a value - so the phone number 1999100200
>becomes 01999100200.
>
[JS] As I mentioned in my previous post, if you put your MySQL output in a
text file and then import it into Excel, you can specify that the phone
number field be imported as text.

It is disconcerting to see a phone number in scientific notation. Even if
you force the format back to all digits, I'm not sure that I'd trust the
last digit (possible rounding issues).
>Of course it's easier for us knowing that a phone number is always 11
>digits ... ;-)
>
>Andy
>
>Quoting Keith Spiller <larentium@stripped>:
>
>> 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
>>
>>
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




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