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

[25 Jan 2016 12:18] Benjamin Hänisch
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.
[25 Jan 2016 14:39] MySQL Verification Team
Please provide a test case table (create table statement, 4/5 insert statements). Thanks.
[29 Jan 2016 10:40] MySQL Verification Team
Please provide the print of the show create table table_name of the file cvs . Thanks.
[29 Jan 2016 12:54] Chiranjeevi Battula
Hello Benjamin Hänisch,

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

Thanks,
Chiranjeevi
[29 Jan 2016 12:55] Chiranjeevi Battula
Screenshot.

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

[11 Nov 2016 20:50] Javier Treviño
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.
[11 Nov 2016 21:41] Javier Treviño
Never mind, I could reproduce this now, and have everything to fix it.
Thanks.
[11 Nov 2016 21:53] Javier Treviño
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.
[5 May 2017 20:58] Rafael Antonio Bedoy Torres
Posted by developer:
 
Fixed on 1.3.7 build 2
[5 May 2017 21:21] Christine Cole
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.
[5 May 2018 15:49] abdenbi hicham
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)
[22 Oct 2018 12:54] Amit Unknown
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.
[8 Jan 2019 11:26] Sven Wargenau
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)
[8 Jan 2019 12:00] Amit Unknown
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.