Why does XLSREAD not replace empty cells by 'NaN' when the cells appear at the end of the range in MATLAB 7.5 (R2007b)?

I am using the following syntax to use XLSREAD:

num = xlsread(filename, 'range')

I have 20 rows of numeric data in an excel sheet and I am specifying the range until 21st row. The last row is empty.

When I use XLSREAD to read the data, the last row is trimmed and it returns only 20 rows in MATLAB. Whereas if a middle row is empty, lets say, 12th row, the elements are filled by 'NaN'. Hence, in the first case also, the 21st row should have been appended to a row of 'NaN's.

Answer 1

[MathWorks Support Team]

XLSREAD is deisnged to trim any empty trailing rows and columns specfied in the range. The matrix returned by XLSREAD can easily be grown in MATLAB to include an additional row or column of NaNs, as shown below:

num = xlsread(filename, 'range');

[m n] = size(num);

num(end+1,:) = nan(1,n);

[Walter Roberson]

The numeric output of xlsread has leading and trailing rows of NaN trimmed out. Leading lines are trimmed under the hypothesis that the corresponding input cells were text headers that failed conversion to numeric. Blank lines are not considered numeric values by excel.

Trailing lines are trimmed under the hypothesis that the numeric section was followed by text that failed conversion to numeric.

Also, excel itself does not appear to differentiate between trailing rows of NaN deliberately placed by the user versus rows that never had anything assigned. Likewise it does not differentiate between trailing columns of NaN deliberately placed by the user versus columns that have never had anything assigned.

If this trimming is a problem for your situation then you should be examining the third output of xlsread(), the raw output.

https://stackoverflow.com/questions/15704538/how-can-i-express-nan-values-in-excel

https://www.excelfunctions.net/Excel-Formula-Error.html

https://stackoverflow.com/questions/11155428/error-how-to-read-empty-cell-in-excel