Why does function 'readtable' ignore string values on cells that appears in the same column as numeric values?

Given is an Excel spreadsheet which has some columns that contain both numeric and string values. When using function 'readtable', then by default the rows that contain the string values are ignored and only the rows that contain the numeric values for the particular column are imported.

When using function 'setvartype' to specify the type of the variables in a column as string, then all the numeric values are ignored instead, which is undesirable. It is desirable to have all the values including the numeric values imported as text.

Why does function 'readtable' ignore string values on cells that appears in the same column as numeric values?

Answer 1

[MathWorks Support Team]

This is the expected behavior when using function 'readtable'. MATLAB through 'readtable' inherits the data types of the cells as they are defined in Excel. It does not perform any pre- or postprocessing on these cells, and by default accepts the numetic variables while ignoring the string variable in case both appear in a column in Excel. By specifying the type of the imported variable to be 'string' using function 'setvartype', MATLAB is instructed to ignore the numeric variables and only import the string variables. For more information, please refer to the following documentation page,

https://de.mathworks.com/help/matlab/ref/matlab.io.text.delimitedtextimportoptions.setvartype.html

Both cannot be imported on the same time, as a column in a MATLAB table has only a particular data type for its entries.

 

The recommended workflow is to convert the entries of a particular column in Excel into string values, see the following link for more information,

https://support.microsoft.com/en-us/office/format-numbers-as-text-583160db-936b-4e52-bdff-6f1863518ba4

and afterwards all values are going to be read-in MATLAB using function 'readtable', since the column contains only string variables.