MySQL Bugs: #80079: Improper handling of Excel date prevents exporting

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:	Windows (10 Pro)
Assigned to:		CPU Architecture:	Any

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.

I have rolled back to Version 1.3.4, which does not exhibit this problem.

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.

Hello Aaron Gibian,

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

Thanks,
Chiranjeevi

Screenshots.

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

Date correctly formatted in excel as DATE, when append Mysql for excel converts the DATE to DATETIME.

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.

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??

Here is a picture of the bug...

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

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.

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

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

Posted by developer:
 
Fixed on 1.3.7 build 2

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.

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

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.