Issue with readtable() reading strings

I'm trying to use readtable() to read in a table of numbers and strings.

If the column of strings has more than half that are "0" or "1" readtable seems to treat the whole column as integer. The data is in the following format:

Start,End,Event,Note

123450000,123450001,ABC,"Event One"

123450001,123450002,DEF,"0"

123450002,123450003,GHI,"1"

If I have 50 rows like this and more than 1/2 have just the "0" or "1" format verus "Event One", even with the quotes and even with adding a format specifier to readtable, it still wants to treat those as integers and gives an error when I try to do a string operation on that 4th column. Shouldn't readtable behavior be if there are any nonintegers then the column is string? Or at least if it sees the double quotes treat it as string? This seems like a bug in readtable.

Answer 1

[Walter Roberson]

filename = 'as appropriate';
opt = detectImportOptions(filename);
opt = setvartype(opt, 'Note', 'char');
T = readtable(filename, opt)

[Michael Nicholas]

Thank you for the quick response. I must be missing something or r2021a behaves differently,

When I tried this, I get an error - unknown variable Note. The file is UTF-8, not UTF-8 BOM if that makes any difference and has the one line header just like in the original post.

As an alternative to changing code, is there something I can do to the format of the data file to trick readtable() into treating it as string? I've tried single quotes and double quotes. The only thing I've found is using a non-numeric character, which I don't want to do.

Answer 2

[Michael Nicholas]

I found someone else posted a similar question already. The behavior changed in R2020a.

https://www.mathworks.com/matlabcentral/answers/1593704-why-does-readtable-generate-nan-values-for-text-data-imported-from-a-file-in-r2020a-or-later?s_tid=srchtitle

Answer 3

[Michael Nicholas]

I was able to get this working with the following:

filename = 'filename.csv';

varNames = {'Start','End','Event','Note'};

varTypes = {'double','double','char','char'};

opts = delimitedTextImportOptions("DataLines",[2 inf],"VariableNames",varNames,"VariableTypes",varTypes);

events = readtable(filename,opts);