trouble reading ordinal categorical variable with missing value using readtable

I am trying to read an Excel file that has an empty cell for a categorical ordered column. For example something like {0 5 [] 3 2 5 4 3 2 1 etc.} where [] indicates a missing value.

My code looks something like:

opts = detectImportOptions(FileName);
opts = setvartype(opts,{'my_column'},'categorical');
opts = setvaropts(opts,{'my_column'},'Ordinal',1);
database = readTable(FileName,opts);

This last line then throws the error:

"To assign a categorical array into another, they must both be ordinal or not ordinal."

Do I need an additional option or does the Excel file need a different variable instead of having an empty cell?

Edit: The issue is that the empty value is showing up as an undefined categorical variable which appears to not be ordinal. When trying to assign this to the ordinal column it throws the above error. Perhaps there is some additional option that needs to be set?

Answer 1

[Guillaume]

Unfortunately, I think you've hit a bug in readtable. Indeed, the problem is because readtable attempts to replace the missing values with a non-ordinal <undefined> category. Ironically, the values are already ordinal <undefined> so if readtable did not attempt this missing value replacement the output would be correct.

I don't think you can work around that with any additional option, there's no do nothing option for missing values so for now the only workaround would be to read the variable as a non-ordinal array, then convert it to ordinal after the call to readtable.

I'll report the bug to mathworks.