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.
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]
