MySQL Bugs: #82497: MySQL Excel connector errs with null fields

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:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S3 (Non-critical)
Version:	1.3.6	OS:	Windows (Windows 10)
Assigned to:		CPU Architecture:	Any

Description:
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.

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?

Thanks,
Chiranjeevi.

Excel Worksheet where error ocurred

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

Explanation

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

Hello Jorge Gallardo,

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

Thanks,
Chiranjeevi

Screenshot

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

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!

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.

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