MySQL Bugs: #80139: Import is interrupted due to date/time value

Bug #80139	Import is interrupted due to date/time value
Submitted:	25 Jan 2016 12:18	Modified:	5 May 2017 21:21
Reporter:	Benjamin Hänisch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S1 (Critical)
Version:	1.3.6	OS:	Windows (Windows 10 Pro)
Assigned to:		CPU Architecture:	Any

Description:
When I try to import a table in excel, which contains different date and timestamps. The import process starts but is then interrupted by a error message: Error copying table data

Details: Unable to convert MySQL date/time value to System.DateTime<00.00.0000> konnte nicht in der pay_f_date-Spalte gespeichert werden. Erwarteter Typ: DateTime.

   bei System.Data.DataColumn.set_Item(Int32 record, Object value)
   bei System.Data.DataTable.NewRecordFromArray(Object[] value)
   bei System.Data.DataTable.LoadDataRow(Object[] values, Boolean fAcceptChanges)
   bei MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)

How to repeat:
Open a new Excel Workbook
select a Database and a table
click "import mysql data"
click "import" without changing the presets
- get error message

Suggested fix:
the issue seem to be, that we have date stamps, which may not be available for every entry and are set to "0000-00-00" in the database in the format yyyy-mm-dd.

Please provide a test case table (create table statement, 4/5 insert statements). Thanks.

Please provide the print of the show create table table_name of the file cvs . Thanks.

Hello Benjamin Hänisch,

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

Thanks,
Chiranjeevi

Screenshot.

Attachment: 80139.PNG (image/png, text), 220.66 KiB.

Hi Benjamin Hänisch, what locale are you using in your computer?
I imported the data you supplied in the SQL script into a table created with your supplied CREATE TABLE statement, but can't reproduce the error, data is imported correctly into Excel.

I am suspecting I can't reproduce the error because either I need to test with a different DateTime format (Windows locale) or a different Office language version.

Can you tell me both your Windows locale and your Office language?
Thanks.

Never mind, I could reproduce this now, and have everything to fix it.
Thanks.

Posted by developer:
 
Added code to differentiate properly a DATE and a DATETIME MySQL data column, this impacts the handling for date and datetime values for Import, Export, Append and Edit data operations.

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:

MySQL columns of type DATE and DATETIME produced errors during import-data
operations. This fix improves the way MySQL for Excel handles these types
for all operations: import data, export data, append data, and edit data.

Thank you for the bug report.

not fixed Yet in version 1.3.7 I tested it through a table containing a Datetime column. this wha i get :

Exception message:
MySqlDataTable.CopyTableData Value '1' is not suitable to be converted to a DateTime data type.

Exception thrown by:
MySqlDataTable.CopyTableData

Stack trace:
   à MySQL.ForExcel.Classes.MySqlDataType.GetValueAsDateTime(Object rawValue)
   à MySQL.ForExcel.Classes.MySqlDataColumn.GetInsertingValueForType(Object rawValue, Boolean escapeStringForTextTypes)
   à MySQL.ForExcel.Classes.MySqlDataTable.PrepareCopyingItemArray(Object[]& itemArray, Boolean escapeFormulaTexts)
   à MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)

Its been 2 years and its still not fixed.

Any random field inside the mysql table gives this error. In one table, it reports this for the ID field, hence the error: Value '1' is not suitable to be converted to a DateTime data type.

In another table is reports this for a different field, hence a similar error: Value 'blahblah' is not suitable to be converted to a DateTime data type.

In neither of the cases are these fields of type date/time.

What a frustrating experience.

This problem still exists!

I specifically noticed it after implementing new stored procedures in my database.

I thought it was fixable by rebooting the db instance but now this doesn't help anymore.  

My database is hosted on a AWS server instance and is up to date. 

I could reproduce the problem with multiple clients with German and English/Icelandic locales. Excel versions "Professional Plus 2016" and "2013". 

Error thrown by Excel plugin:
 
Exception message:
Value '2017102' is not suitable to be converted to a DateTime data type.

Exception thrown by:
MySqlDataTable.CopyTableData

Stack trace:
   bei MySQL.ForExcel.Classes.MySqlDataType.GetValueAsDateTime(Object rawValue)
   bei MySQL.ForExcel.Classes.MySqlDataTable.PrepareCopyingItemArray(Object[]& itemArray, Boolean escapeFormulaTexts)
   bei MySQL.ForExcel.Classes.MySqlDataTable.CopyTableData(DataTable fromSourceTable, Boolean preserveChanges)

Sven see this https://bugs.mysql.com/bug.php?id=93501. Since this one was closed, I opened a new one. Pls add your comment there so that the support team can see.