Why is ReadTable Deleting Data with NaN?

7 views (last 30 days)
I'm trying to automate the reading of several hundred excel files with identical layouts using a for loop and readtable.
Does anyone know why readtable is removing data in only SOME of my columns and replacing them with NaN? I'm a bit of an amateur, so I would appreciate any help.
Excel Spreadsheet
Matlab Line
teeth = readtable(file,'Sheet','Teeth Form Sheet','Range',''); %This reads the excel file and a particular sheet and should import all the data
Table Output
  2 Comments
Alan Lee
Alan Lee on 12 Aug 2022
Sorry to waste your time Cris.

Sign in to comment.

Accepted Answer

Alan Lee
Alan Lee on 12 Aug 2022
I figured it out. The issue was that not all my data points were the same Data type and that Readtable was getting confused. Once I converted all the data to Characters there were no more issues,
opts = detectImportOptions(file,'Sheet','Teeth Form Sheet','Range','');
opts = setvartype(opts,'char');
teeth = readtable(file,opts);

More Answers (1)

Cris LaPierre
Cris LaPierre on 12 Aug 2022
The columns of a table must all be of the same datatype. readtable has set the datatype for each column based on the datatype in the first row. When the datatype is set to numeric and non-numeric information is encountered, it is replaced with NaN, meaning "not a number".
It looks to me like the data you want to read in is actually in rows 2-4 and 7-9. You can use the import tool to interactive create your import, or the options field of readtable.
  1 Comment
Alan Lee
Alan Lee on 12 Aug 2022
Spot on Cris. I didn't think to look at that until I'd already uploaded.

Sign in to comment.

Products


Release

R2020b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by