Matlab xlsread: force empty cells to be read as NaN

0
votes

I need to import data from a square region (10 by 10 cells) on an Excel sheet into Matlab.

All data in the region are numerical, but some outer rows and columns of the region are empty.

In Matlab I still want to have a 10 by 10 matrix of doubles with NaNs in places where there are empty cells in Excel (also in outer rows and columns).

If I use xlsread then empty outer rows and columns are automatically truncated.

Needless to say that all should be done automatically without the knowledge how many empty outer rows and columns are there.

How can I do this?

1
matlabioxlsread
Did you check the second output argument of xlsread? – Daniel
@Daniel, yep, it is empty. – Sergey Zykov
Found on matlab central: num = xlsread(filename, 'range'); [m n] = size(num); num(end+1,:) = nan(1,n); – C.Colden
@C.Colden I think you manually pad num with an additional row at the end, but what if you do not know how many outer extra NaNs rows and columns you need to attach because everything should be automatic. – Sergey Zykov
I do not fully understand your problem. What do you mean with outer rows/columns. Could you edit your question and add some examples? – C.Colden

1 Answers

0
votes

Let's say your 10 by 10 spreadsheet's first row and column and last row and column are empty (like this). Using:

[num,txt,raw] = xlsread('myfile.xlsx',1,'A1:J10'); % Read input.

will return:

num   8x8 double
txt   0x0 cell
raw   10x10 cell

In num, non-scalar leading rows and columns are automatically truncated, while in txt any numerical values are omitted. However, raw contains all information, so it can be used to extract the numerical values:

raw(cellfun(@ischar,raw)) = {NaN}; % Set non-scalar values to missing.
A = cell2mat(raw); % Convert to matrix.