>From: Andy Shellam [mailto:andy-lists@stripped]
>Sent: Friday, August 22, 2008 4:39 AM
>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
[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 ... ;-)
>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.
>> ----- Original Message ----- From: "Kralidis,Tom [Burlington]"
>> To: "Keith Spiller" <larentium@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
>>> Of course, you could write a post-processing script that adds the
>>> leading zeros to records not long enough.
>>> -----Original Message-----
>>> From: Keith Spiller [mailto:larentium@stripped]
>>> Sent: Thu 21-Aug-08 22:11
>>> To: mysql@stripped
>>> Subject: Zip Codes with Leading Zeros
>>> 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.
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1