MySQL Bugs: #76738: Field won't retain leading zeros

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:	Windows (v8.1)
Assigned to:		CPU Architecture:	Any

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.

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.

I'm using Excel 2010.

Please provide the SHOW CREATE TABLE table_name output and 1/2 insert example. Thanks.

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.

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.

leading zeros

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

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)

I see the screen shot, but what should I be gleaning from it?

I attached the screen shot just to show the bug (now the bug report is on Verified status). Thanks.

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.

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.

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?

Blair, are you referring to MySQL for Excel 1.3.6?

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.

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.

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.

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!