MATLAB: Does “readtable” improperly read Excel data with “N/A” values, row names, and headers

charactersdetectimportoptionsimportMATLABnannumbersoptionsreadtablexlsx

I am trying to read my Excel table, which includes headers, row names, and numeric data, into MATLAB. However, when I use the "readtable" function, my data is imported incorrectly. Here are the specific issues I'm encountering:
  1. The data are all cells of character vectors instead of doubles.
  2. Missing data is labeled as "N/A" in Excel. I want these values to show up as NaNs in MATLAB.
  3. My Excel table has column and row names that I want to be imported, as well.

Best Answer

To customize the way MATLAB imports your table from Excel, supply "readtable" with a second argument that specifies the import options, "opts":
https://www.mathworks.com/help/matlab/ref/readtable.html?s_tid=doc_ta#d117e1212973
To generate these options, use the "detectImportOptions" function, making sure to include the .xlsm extension at the end of your filename:
https://www.mathworks.com/help/matlab/ref/detectimportoptions.html?s_tid=doc_ta#d117e309599
This should take care of most of your issues, including converting all your data to numbers and changing "N/A" values to NaNs. However, there are several additional Name/Value pairs you will have to pass into the "detectImportOptions" function. Specifically, you will need to specify where your variable names, row names, and data entries start using some of the "Parameters for Spreadsheet Files Only" listed in the documentation:
https://www.mathworks.com/help/matlab/ref/detectimportoptions.html?s_tid=doc_ta#d117e309599
Here are the properties you will need to specify:
  • Set "DataRange" to "B2" since your data start in cell "B2" of your Excel sheet
  • Set "RowNamesRange" to "A2" since your row names start in cell "B2" of your Excel sheet
  • Set "VariableNamesRange" to "B1" since your column headers start in cell "B2" of your Excel sheet
Make sure to specify these properties as Name/Value pairs when initially calling the "detectImportOptions" function.
Here's an example of how to implement the steps above using the attached file "example.xlsm":
filename = "example.xlsm";
opts = detectImportOptions(filename,'DataRange','B2','RowNamesRange','A2','VariableNamesRange','B1');
tb = readtable(filename,opts)
Note how different this is from the initial attempt to use "readtable" without the input options object:
filename = "example.xlsm";
tb = readtable(filename)
Related Question