MySQL Bugs: #72147: Cannot append a blank or NULL value to a nullable MySQL field

Bug #72147	Cannot append a blank or NULL value to a nullable MySQL field
Submitted:	27 Mar 2014 18:30	Modified:	8 Aug 2014 2:01
Reporter:	Matt Meridius	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.2.0	OS:	Windows (Win 8.1 64bit MySQL 5.6.16.0)
Assigned to:		CPU Architecture:	Any

Description:
Excel table has a few number fields that occasionally need to be NULL (I've tried using "" or "NULL" in the Excel formula that populates this field).  Unable to append these tables when NULL exists.  The error message is either 

"Input string was not in a correct format. Couldn't store <> expected type is int64."

OR

"Cannot set [column name] to be null. Please use DBNull instead."

I've tried every possible permutation of Advanced Options settings while changing the excel format of the data (i.e., number category as general, number, scientific, etc).  The fields where this is failing are int64 and nullable.

What can I use in the blank Excel fields so that the MySQL for Excel tool will successfully translate it to a NULL value in the database?  In prior version of MySQL for Excel, I used NULL but that no longer works in 1.2.0.  Help!!

How to repeat:
Create an Excel table with blanks or the word NULL in some of the cells.  Append it to a MySQL database where the corresponding fields are integers and nullable.

Both blank and NULL values will produce errors when attempting to append.

Suggested fix:
When the Excel value is blank or NULL, MySQL for Excel should use the DBNull value MySQL apparently requires.

Upgraded to MySQL for Excel 1.2.1. Didn't see a fix for my problem in the release notes but figured it was worth a shot anyway. Problem still exists :(

On a quick test I could perfectly use blank values to insert them to a nullable integer column.  The word NULL does not work by design, even in Excel it is not used for null values, the standard is to use an empty string.

If you are using a formula please provide the exact formula to reproduce your scenario.

Thanks for responding. An example of the formula is:

=IFERROR(match(-- lookup value --),"")

If the match formula finds a number (always a two or three digit number) then the number is used. If it doesn't, the formula above yields a null string in the cell.

But this didn't work in MySQL for Excel 1.1.0 so I changed the formula to

=IFERROR(match(-- lookup value --),"NULL") and it worked fine. 

I'm aware that NULL isn't used by excel as an operative value. I'm creating formulas to produce data that will transfer to MySQL successfully and this is what worked. The idea to try this was based on the fact that when I exported fields from the database, MySQL was exporting the word NULL for null fields. So when a null string didn't work I decided to try giving NULL back to it when needed.

On MySQL for Excel 1.2.0 and 1.2.1 neither option works. In fact after my last post the append function didn't work at all.  If it's working for you then perhaps there's a version compatibility issue? For my part, I'm running Office Home and Student 2010 (x64) and win 8.1 x64. Current installed software is:

Microsoft Visual Studio 2010 Tools for Office Runtime (x64) 10.0.31117

MySQL Connector C++ 1.1.3
MySQL Connector J 5.1.29
MySQL Connector Net 6.8.3
MySQL Connector/C 6.1.3
MySQL Connector/ODBC 5.2.6
MySQL Documents 5.6.16
MySQL Examples and Samples 5.6.16
MySQL Installer	1.1.5.0
MySQL Notifier 1.1.5
MySQL Server 5.6.16
MySQL Utilities	1.3.6
MySQL Workbench CE 6.0.9
MySQL For Excel 1.1.0 (I backed off to this due to 1.2.0 problems)

As I've noted in another post about installation problems, the installer msi didn't flag any missing or incompatible software, neither when I installed the entire 5.6.16 package (which included 1.2.0) or when I upgraded to 1.2.1 using the standalone.

Hi Matt, thanks for the bug report. I verified it and it is actually related to formulas; values returned by formulas are not being recognized by MySQL for Excel so they cannot be appended to an existing MySQL table.

Since the problem is related to ALL values coming from formulas, and not to null values, which affect both Append and Export Data operatons, I created the following other bug and will close this one, please subscribe to this bug now if you wish to be notified when it gets fixed:
http://bugs.mysql.com/bug.php?id=73505

Following up with the null values, after the bug I mentioned is fixed, your formulas should return an empty string ("") when your intention is to insert a null value.

Since the problem's scope is bigger than described in this bug, this bug is being flagged as duplicate of this other one:
http://bugs.mysql.com/bug.php?id=73505