Bug #80079 Improper handling of Excel date prevents exporting
Submitted: 20 Jan 2016 17:06 Modified: 5 May 2017 21:36
Reporter: Aaron Gibian Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.6 OS:Microsoft Windows (10 Pro)
Assigned to: CPU Architecture:Any

[20 Jan 2016 17:06] Aaron Gibian
Description:
** I am trying to export 11 columns by 67+1(col_name) rows:
| Type           | Null | Key | Default |
+----------------+------+-----+---------+
| tinyint(1)     | YES  |     | NULL    |
| varchar(5)     | YES  |     | NULL    |
| varchar(12)    | NO   | PRI | NULL    |
| varchar(255)   | YES  |     | NULL    |
| decimal(65,30) | YES  |     | NULL    |
| date           | YES  |     | NULL    |
| decimal(65,30) | YES  |     | NULL    |
| varchar(45)    | YES  |     | NULL    |
| varchar(255)   | YES  |     | NULL    |
| varchar(4000)  | YES  |     | NULL    |
| varchar(45)    | YES  |     | NULL    |
+----------------+------+-----+---------+

** I receive the following error message:

"Error while inserting rows...
MySQL Error 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12:00:00 AM,0,'n/a','Person Name',null,null)' at line 1."

** This error message was produced after selecting "Generate Insert Statement for Every Row". The same error is produced without separating.

** The error is caused by the first column displayed in the error--the time--because operation works fine when that column is omitted.

** The column should be DATE not time. The column is formatted as date within Excel and I selected date (not DateTime or Time) for the MySQL column type on the exporting dialog box.

** I cannot find a way to format the cells containing the date that works to pass the data without manually retyping the data. 

**The error is not produced when you allow DateTime date type. I may investigate this but using this datatype would require additional manipulation any time I want to use or manipulate the date data later and also increase the database size unnecessarily. (For my edification, does that rise to the level of Critical Severity?)

How to repeat:
Like the boolean data type error in 1.3.5, reproducing the error only requires that you try to pass a date formatted cell and choose the date data type for the column in MySQL.
[20 Jan 2016 18:04] Aaron Gibian
I have rolled back to Version 1.3.4, which does not exhibit this problem.
[21 Jan 2016 7:41] Chiranjeevi Battula
Hello Aaron Gibian,

Thank you for the bug report.
I tried to reproduce the issue at my end using MS Excel 2013, MySQL for Excel 1.3.6 but not seeing any issues with date data type.
Could you please provide repeatable test case (exact create table statement, sample excel data etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[22 Jan 2016 6:26] Chiranjeevi Battula
Hello Aaron Gibian,

Thank you for your feedback.
Verified this behavior on MS Excel 2013, MySQL for Excel 1.3.6.

Thanks,
Chiranjeevi
[22 Jan 2016 6:27] Chiranjeevi Battula
Screenshots.

Attachment: 80079.zip (application/zip, text), 514.70 KiB.

[17 Feb 2016 14:33] Dawid Poolman
Date correctly formatted in excel as DATE, when append Mysql for excel converts the DATE to DATETIME.
[15 Jul 2016 15:19] Chengxi Wang
I created a table with column as Date in workbench, then use 'append excel data to table' option from the plugin to upload a row correctly formatted in YYYY-MM-DD.
The same error message appeared.
[12 Aug 2016 15:58] Léonard Maguin
Hi,
I thik this is the source of the bug: There is missing quotes in the auto-generated MySQL insert statement.

Values should be:
(1,"2014-09-01","07:00:00","17:00:00");

Instead it auto-generates the wrong query below without quotes (and with 00:00:00 in the date):

INSERT INTO `presence_quotidienne`
(`id`,`date`,`heure_arrivee`,`heure_sortie`)
VALUES
(1,2014-09-01 00:00:00,07:00:00,17:00:00);

Did anyone found a workaround??
[12 Aug 2016 16:14] Léonard Maguin
Here is a picture of the bug...

Attachment: bug_mysqlforexcel.JPG (image/jpeg, text), 259.28 KiB.

[16 Aug 2016 19:46] Aaron Gibian
For Léonard Maguin, I made this comment earlier in the thread and it appears to be public:

[20 Jan 18:04] Aaron Gibian
I have rolled back to Version 1.3.4, which does not exhibit this problem.

Worth noting that I am still using version 1.3.4.
[11 Nov 2016 19:07] Javier Treviño
Posted by developer:
 
Fixed the code that translates datetime values being exported/appended to a MySQL table depending on the target column data type.
Excel values are always stored internally as a number corresponding to a datetime type, MySQL for Excel now correctly converts the value to a string representation based on the target type (DATE or DATETIME).
[13 Jan 2017 22:42] jonathan mckee
Hi Developer,

This is still an issue with excel 2013 wins 7.

Version 1.3.6 on 86 architecture.

Date which is formatted to yyyy/mm/dd in excel and displays correctly  shows up as datetime  with the date inserted as dd/mm/yyyy and 00:00:00  afterwards error comes up on insert

Have you any work arounds or ideas ?

regards

Jonathan
[5 May 2017 20:55] Rafael Antonio Bedoy Torres
Posted by developer:
 
Fixed on 1.3.7 build 2
[5 May 2017 21:36] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.7 release, and here's the changelog entry:

Excel data of type Date could not be exported to a MySQL table.

Thank you for the bug report.
[29 Jan 2018 16:54] Steve Sanda
This issue exists for columns where data is translated for TIME as well. I am using MySQL for Excel 1.3.7 and am experiencing the same issue as noted in the original bug (DATE and DATETIME). My column is using TIME data type and the same error occurs.

Error while inserting rows...

MySQL Error 1064:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':30:00,20:10:00,160),
(2,18674,'NEUR 4200','Adv Neuro Lab','2018-01-10 00:00:00' at line 4
[19 Jul 2018 19:13] Natalja Cer
I confirm that I have been experiencing the same issue with 1.3.7. It is a very annoying bug.
It seems that, while updating the entry, it looses the two numbers before ":", so every time I update/make and entry with a TIME (e.g. 14:20:00), I get the error saying Error nr. ... <...> ":20:00" <...>. So the "14" is lost in the update procedure.