MATLAB: Readtable ommits NaNs at top of column

MATLABnanreadtabletreatasmissingxlsx

Dear Matlab community,
I want to use readtable to read from an xlsx file with NaNs. when NaNs apear in a column, as in the example spreadsheet column A, it works and they are appear in the tabel opject as desired. However when there are NaNs at the top of the column, as in the example spreadsheet column B, they just get ommitted. I do want to read them as well though to get the right length of the table column.
testA=readFromTable("test.xlsx",'Sheet1','A1:A9');
testB=readFromTable("test.xlsx",'Sheet1','B1:B9');
with the function:
function data = readFromTable(path, sheet, range)   
    opts = detectImportOptions(path,'Sheet', sheet,'Range',range,'NumHeaderLines',0);
    opts = setvaropts(opts, 'TreatAsMissing',{'.','NA','NAN','none','NaN','Nan','nan','x','',' '});
    data=readtable(path,opts);
end
I am really confused over this already. Any help is much appreciated.
Kind regards,
Max

Best Answer

Hi,
this worked for me:
testA=readFromTable("test.xlsx",'Sheet1','A1:A9');
testB=readFromTable("test.xlsx",'Sheet1','B1:B9');
function data = readFromTable(path, sheet, range)   
    opts = spreadsheetImportOptions('DataRange',range, 'Sheet', sheet, 'VariableTypes','double');
    data=readtable(path,opts);
end
Best regards
Stephan
Related Question