Bug #82497 MySQL Excel connector errs with null fields
Submitted: 8 Aug 2016 21:46 Modified: 4 May 2017 16:55
Reporter: Jorge Gallardo Rius Email Updates:
Status: Closed Impact on me:
Category:MySQL for Windows: MySQL for Excel Severity:S3 (Non-critical)
Version:1.3.6 OS:Microsoft Windows (Windows 10)
Assigned to: CPU Architecture:Any

[8 Aug 2016 21:46] Jorge Gallardo Rius
Loading an excel table, I connected to the schema, selected the database and proceeded to export Excel data to a new table.  During the load operation, it gave me an error saying I had a syntax error near ')'.

I found that the error ocurred on rows that had null fields. When I deleted those rows, the table creation worked fine.

How to repeat:
Try running with a table that has many rows but, somewhere in the middle, there are rows that have the two last fields null.
[9 Aug 2016 6:46] Chiranjeevi Battula
Hello Jorge Gallardo,

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 in importing data from a table with null field.
Could you please provide repeatable test case (sample excel data,screenshot, etc. - please make it as private if you prefer) to confirm this issue at our end?

[9 Aug 2016 10:29] Jorge Gallardo Rius
Excel Worksheet where error ocurred

Attachment: Clientes Para Revisión CIIU (1).xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, text), 172.30 KiB.

[9 Aug 2016 10:30] Jorge Gallardo Rius

Attachment: Bug Test.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 571.78 KiB.

[9 Aug 2016 12:06] Chiranjeevi Battula
Hello Jorge Gallardo,

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

[9 Aug 2016 12:06] Chiranjeevi Battula

Attachment: 82497.JPG (image/jpeg, text), 242.41 KiB.

[1 Dec 2016 23:26] Javier Treviño
This was really a hard bug to track.
Normally empty cells are translated by Excel interop code to null values internally.  The two cells in the specific example in the attached spreadsheet look like empty cells, but they are not empty.

If you apply a formula of "=CELL("type", D268)" in the attached spreadsheet, it returns "l", where as "=CELL("type", E268)" returns "b".
D268 is not really blank although it looks like that, I don't really know how that value that looks like blank got inserted there, but clearing out the field is the true workaround, so the data is properly exported to MySQL.

I will fix this anyway to consider this uncommon scenario.
Thanks for the very detailed bug report!
[2 Dec 2016 1:47] Javier Treviño
Posted by developer:
Fixed the case where Excel cells contain an empty string instead of a blank, maybe coming from pasted data, so that when the data is processed to be inserted into a MySQL table (trhough an Export, Append or Edit Data operation) the emtpy string is considered the same as a blank cell.
[3 May 2017 20:14] Rafael Antonio Bedoy Torres
Posted by developer:
Fixed on 1.3.7 build 2
[4 May 2017 16:55] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL for Excel 1.3.7 release, and here's the changelog entry:

Excel cells containing empty strings, which are not actually blank,
generated errors with export, append, or edit data operations. With this
fix, an empty string is now equivalent to a blank cell.

Thank you for the bug report.