>-----Original Message----- >From: Andy Shellam [mailto:[EMAIL PROTECTED] >Sent: Friday, August 22, 2008 4:39 AM >To: mysql@lists.mysql.com >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 <[EMAIL PROTECTED]>: > >> 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]" >> <[EMAIL PROTECTED]> >> To: "Keith Spiller" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> >> 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:[EMAIL PROTECTED] >>> Sent: Thu 21-Aug-08 22:11 >>> To: mysql@lists.mysql.com >>> 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/[EMAIL PROTECTED] >> >> >> > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
