Imported data becomes NaN
Show older comments
I'm trying to import data from an excel document with many rows and columns.
When I import it in any way Matlab gives Nan.
Ex. 1. I import from excel as Table (also tried column vectors, column vectors and numeric matrix), If I open it in the workspace I see numbers. When loading in matlab command window it becomes NaN all of it.
Ex 2. I save the excel into txt files and other various file types and try to use "loadtable", "readcell", "load" but it becomes "NaN" or "1x1 missing".
Does anyone have an idea of what I'm doing wrong?
Update, I can now see the reason for my troubles are that the imported file gives many NaN and I didn't realise I had so many NaN in the end. All columns are not the same length. Now I just need to remove remove all NaN. I assume I need to take each column and remove nan for that column.
4 Comments
dpb
on 17 Oct 2022
Not without seeing either of the actual code used nor the file, no.
But, the problem will more than likely lie in just what the content of the Excel file actually is.
One thing to try until you can post back the needed information outlined above is detectImportOptions
Robin Nilsson
on 17 Oct 2022
Stephen23
on 17 Oct 2022
Robin Nilsson
on 17 Oct 2022
Accepted Answer
opt=detectImportOptions(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
tCA=readtable(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
head(tCA)
Looks ok excepting more than likely there are additional header lines in the file besides the first...let's see what the file really, really contains...
CA=readcell(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
CA(1:5,:)
And, indeed, that's what we see -- there's another pair of lines prior to the actual data that is metadata apparently; one has to recognize what is in the file one is trying to read and adjust expectations to fit reality.
In this case, I'd suggest modifying the optons object slightly first before importing -- since the percentages are embedded in the first header row, one can extract them later and then just begin reading the real data after skipping the additional two header lines. That would look like--
opt.DataRange='A4'; % keeping the opt object already read, just fixup where data starts
tCA=readtable(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'),opt);
head(tCA)
And, voila! joy ensues...to get the percentages for each pair of observations is easy enough...
pct=str2double(extract(tCA.Properties.VariableNames(1:2:end),digitsPattern))
1 Comment
Robin Nilsson
on 17 Oct 2022
More Answers (1)
Kevin Holly
on 17 Oct 2022
0 votes
I would suggest using the Import Data button found on the toolstrip.
After selecting the Excel spreadsheet to open, it should give you a preview and show what cells are unimportable. By default these cells are converted to NaN values. This could help you resolve the problem.
3 Comments
Robin Nilsson
on 17 Oct 2022
This I've tried. If I take import data, choose excel. I get this (last picture?), here called RobinDVSdataevaluationS8. I import it. The text will become NaN and the numbers stay (Image 2). When I run it in command window it gives only Nan. (image 1). Is this what you mean?
Robin Nilsson
on 17 Oct 2022
dpb
on 17 Oct 2022
The import options object has ways to handle those as well; you can either use the default missing value and get the NaN or have any records with missing data skipped entirely, or a plethora of other options.
Categories
Find more on Spreadsheets in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
