Bug #76738 Field won't retain leading zeros
Submitted: 17 Apr 2015 13:18 Modified: 14 Jan 2016 14:12
Reporter: Blair Benjamin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.4 OS:Microsoft Windows (v8.1)
Assigned to: CPU Architecture:Any

[17 Apr 2015 13:18] Blair Benjamin
Description:
I just started using MySQL for Excel in order to manage my MySQL data and databases with Excel. I connected successfully, but when I chose the option to Edit MySQL data, it loaded my data fine but one of my fields (the primary key) had leading zeros stripped out by Excel. The field is Varchar in my MySQL database.  It is imperative that the leading zeros remain, as they represent ID numbers. I posted this concern to the support forum and the response I got was that this is indicative of a bug in the software. Hopefully you can fix it. 

Thanks!

How to repeat:
Have a Varchar field in MySQL that has numberic content with leading zeros and load the data for editing in Excel. 

Suggested fix:
Make it so that the leading zeros are retained for varchar / text fields.
[17 Apr 2015 13:37] Miguel Solorzano
Thank you for the bug report. Please provide your Excel version, a small dump file of your table with insert data statement and a screenshot of the issue. Thanks.
[17 Apr 2015 14:13] Blair Benjamin
I'm using Excel 2010.
[17 Apr 2015 14:29] Miguel Solorzano
Please provide the SHOW CREATE TABLE table_name output and 1/2 insert example. Thanks.
[17 Apr 2015 16:42] Miguel Solorzano
Ni isn't enough the picture and I am not sure i.e.: the column CustID is a mix of numbers and letter, so I would like to see how you inserted these values. Thanks.
[17 Apr 2015 16:57] Blair Benjamin
This is the command that was used - 

Here is the SHOW CREATE TABLE results for Students:
                      CREATE TABLE `Students` (   `people_id` varchar(9) NOT NULL DEFAULT '',   `fullname` varchar(50) DEFAULT NULL,   `first_name` varchar(20) DEFAULT NULL,   `middle_initial` char(1) DEFAULT NULL,   `last_name` varchar(25) DEFAULT NULL,   `nickname` varchar(20) DEFAULT NULL,   `email` varchar(50) DEFAULT NULL,   `program` varchar(20) DEFAULT NULL,   `residency` varchar(1) DEFAULT NULL,   `phone` varchar(30) DEFAULT NULL,   `class_level` varchar(40) DEFAULT NULL,   `campus` varchar(45) DEFAULT NULL,   `address_line_1` varchar(40) DEFAULT NULL,   `city` varchar(30) DEFAULT NULL,   `state` varchar(4) DEFAULT NULL,   `zip` varchar(15) DEFAULT NULL,   `lookup_status` varchar(50) DEFAULT NULL,   PRIMARY KEY (`people_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

It should be noted, however, that the field I'm concerned about is the people_id field moreso than the CustID field.  However, I really need the Excel MySQL plugin to not strip leading zeros as a general rule.  For example, I noticed that a few zip codes that start with 0 also lost their leading zero, which is obviously a problem.  I cropped that field from the screen shot for the privacy of the users since it is live data, but the point remains.  The data is fine on the MySQL side, but when I open it in Excel, leading zeros are gone from any fields that have them.
[17 Apr 2015 17:33] Miguel Solorzano
leading zeros

Attachment: 01_zeros.png (image/png, text), 16.21 KiB.

[17 Apr 2015 17:35] Miguel Solorzano
Thank you for the feedback. See prior attached screenshot.

mysql> SHOW CREATE TABLE leadingzeros\G
*************************** 1. row ***************************
       Table: leadingzeros
Create Table: CREATE TABLE `leadingzeros` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `people_id` varchar(9) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO leadingzeros (people_id) VALUES ("000165331"), ("000165624"), ("000165038");
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM leadingzeros;
+----+-----------+
| id | people_id |
+----+-----------+
|  1 | 000165331 |
|  2 | 000165624 |
|  3 | 000165038 |
+----+-----------+
3 rows in set (0.00 sec)
[17 Apr 2015 17:39] Blair Benjamin
I see the screen shot, but what should I be gleaning from it?
[17 Apr 2015 17:52] Miguel Solorzano
I attached the screen shot just to show the bug (now the bug report is on Verified status). Thanks.
[9 Dec 2015 0:34] Javier Treviño
Posted by developer:
 
Fixed code for data import operations done to an Excel range so that numeric data stored in text columns is correctly formatted by Excel as text and not as the "General" format that automatically formats cells as numbers when the data is stored in a text object.

This fixes the problem seen in Edit Data operations where text columns were being formated as numbers and leading zeros stripped from them.
[15 Dec 2015 22:20] Philip Olson
Fixed as of the upcoming MySQL for Excel 1.3.6 release, and here's the changelog entry:

Numeric data stored in text columns is now correctly formatted by Excel as
text, and not as the "General" format that automatically formats cells as
numbers when the data is stored in a text object. This fixes the issue
where text columns were formatted as numbers, which stripped leading
zeros.

Thank you for the bug report.
[13 Jan 2016 18:44] Blair Benjamin
Any idea when the fixed version will be available or released?  Seems like right now the version that is available for download is still the one that has this problem.  I'd love to be able to download the fixed version.  What are the prospects?
[13 Jan 2016 20:54] Philip Olson
Blair, are you referring to MySQL for Excel 1.3.6?
[13 Jan 2016 21:09] Blair Benjamin
Yes. The last comment suggested that this fix would be a part of v1.3.6 which was specified to be "upcoming".  Has it been released?  I just downloaded the version that is available, and it seems to be 1.3.4 still.  Although admittedly I'm not seeing where the version is identified when I'm in Excel using the plugin.
[13 Jan 2016 22:46] Philip Olson
Strange. The MySQL for Excel download page shows 1.3.6:

http://dev.mysql.com/downloads/windows/excel/

Perhaps you are using the "MySQL Installer for Windows" application to manage your MySQL products? If so, a product catalog update might cause 1.3.6 to be present there.
[14 Jan 2016 1:27] Javier Treviño
Version 1.3.6 has been already published, you can download the stand-alone
MSI from http://dev.mysql.com/downloads/windows/excel/
It looks like the MySQL Installer for Windows is not picking up this version
correctly as the latest version shown there is 1.3.5.  Will report this to
the MySQL Installer team since this is a bug.
[14 Jan 2016 14:12] Blair Benjamin
Yes, I was using the Windows installer package. However, I just used the link you provided and got the latest version installed.  Works great and the problem is resolved.  Thanks for the great support and for getting this fixed!