Why does MLPutMatrix export blank cells as 0 rather than NaN using Excel Link 2.2.1 (R14SP1)?

If I use the MLPutMatrix command with a numeric group of cells containing empty cells, the empty cells are sent to MATLAB as zeroes. However, I was expecting them to be converted to NaNs in MATLAB.

Answer 1

[MathWorks Support Team]

This feature has been added in Excel Link 2.5 (R2007a). For more information on this feature execute the following command at the MATLAB command prompt

doc MLMissingDataAsNaN

If you are using a previous version, read the following:

The ability to have MLPutMatrix import blank cells as NaN is not available in Excel Link. The expected behavior is that MLPutMatrix imports blank cells as 0 when the group of cells contains numeric data. The documentation for MLPutMatrix explains:

Empty numeric data cells within the range of mdat become numeric zeros within the MATLAB matrix identified by var_name. If any element of mdat contains string data, mdat is exported as a MATLAB cell array. Empty string elements within the range of mdat become NaNs within the MATLAB cell array.

As a result, if you wish for blank cells to be imported as NaN, you can enter a string into one of the cells you are importing. This will cause the block of cells to be exported into a cell array and for blank cells to be exported as NaN.