0 votes

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 commentaires
Afficher 2 commentaires plus anciens Masquer 2 commentaires plus anciens

dpb
dpb le 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
Robin Nilsson le 17 Oct 2022
Hi. I posted some pictures below on Kevins answer. Import data in that example.
Stephen23
Stephen23 le 17 Oct 2022
@Robin Nilsson: upload the actual data file by clicking the paperclip button.
Robin Nilsson
Robin Nilsson le 17 Oct 2022
Sure. Here it is.

Connectez-vous pour commenter.

Connectez-vous pour répondre à cette question.

 Réponse acceptée

dpb
dpb le 17 Oct 2022
Ran in:

0 votes

Ran in:
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)
CA10 CA10abs CA20 CA20abs CA30 CA30abs CA40 CA40abs CA50 CA50abs CA60 CA60abs CA70 CA70abs CA80 CA80abs CA70d CA70absd CA60d CA60absd CA50d CA50absd CA40d CA40absd CA30d CA30absd CA20d CA20absd CA10d CA10absd CA0d CA0absd ____ _________ ____ _______ ____ _______ ____ __________ ____ _________ ____ _______ ____ _______ ____ _______ _____ ________ _____ _________ _____ ________ _____ ________ _____ ________ _____ ________ _____ ________ ____ _______ 10 NaN 20 NaN 30 NaN 40 NaN 50 NaN 60 NaN 70 NaN 80 NaN 70 NaN 60 NaN 50 NaN 40 NaN 30 NaN 20 NaN 10 NaN 0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.02 -0.001441 0.02 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.01 0 0.04 0 0.04 0 0.03 0 0.04 -0.0014059 0.03 0 0.03 0 0.04 0 0.03 0 0.04 0 0.03 0 0.03 0 0.03 0 0.04 0 0.03 0 0.04 0 0.03 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.06 0 0.05 0 0.05 0 0.05 0 0.06 0 0.05 0 0.06 0 0.05 0 0.07 -0.001441 0.07 0 0.07 0 0.07 0 0.07 -0.001395 0.07 0 0.07 0 0.07 0 0.07 0 0.06 0 0.07 0 0.07 0 0.08 0 0.07 0 0.07 0 0.07 0 0.09 -0.001441 0.09 0 0.09 0 0.09 0 0.09 -0.001395 0.08 0 0.09 0 0.09 0 0.09 0 0.08 0.0013491 0.08 0 0.09 0 0.09 0 0.08 0 0.09 0 0.08 0
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,:)
ans = 5×32 cell array
{'CA10' } {'CA10abs'} {'CA20' } {'CA20abs'} {'CA30' } {'CA30abs'} {'CA40' } {'CA40abs'} {'CA50' } {'CA50abs'} {'CA60' } {'CA60abs'} {'CA70' } {'CA70abs'} {'CA80' } {'CA80abs'} {'CA70d' } {'CA70absd'} {'CA60d' } {'CA60absd'} {'CA50d' } {'CA50absd'} {'CA40d' } {'CA40absd'} {'CA30d' } {'CA30absd'} {'CA20d' } {'CA20absd'} {'CA10d' } {'CA10absd'} {'CA0d' } {'CA0absd'} {[ 10]} {'%' } {[ 20]} {'%' } {[ 30]} {'%' } {[ 40]} {'%' } {[ 50]} {'%' } {[ 60]} {'%' } {[ 70]} {'%' } {[ 80]} {'%' } {[ 70]} {'%' } {[ 60]} {'%' } {[ 50]} {'%' } {[ 40]} {'%' } {[ 30]} {'%' } {[ 20]} {'%' } {[ 10]} {'%' } {[ 0]} {'%' } {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]'} {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]' } {'Time' } {'Abs [%]'} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[ 0]} {[0.0200]} {[-0.0014]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0100]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0100]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0200]} {[ 0]} {[0.0100]} {[ 0]} {[0.0200]} {[ 0]} {[0.0100]} {[ 0]}
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)
CA10 CA10abs CA20 CA20abs CA30 CA30abs CA40 CA40abs CA50 CA50abs CA60 CA60abs CA70 CA70abs CA80 CA80abs CA70d CA70absd CA60d CA60absd CA50d CA50absd CA40d CA40absd CA30d CA30absd CA20d CA20absd CA10d CA10absd CA0d CA0absd ____ _________ ____ _______ ____ _______ ____ __________ ____ _________ ____ _______ ____ _______ ____ _______ _____ ________ _____ _________ _____ __________ _____ ________ _____ ________ _____ ________ _____ ________ ____ _______ 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.02 -0.001441 0.02 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.02 0 0.02 0 0.01 0 0.02 0 0.01 0 0.04 0 0.04 0 0.03 0 0.04 -0.0014059 0.03 0 0.03 0 0.04 0 0.03 0 0.04 0 0.03 0 0.03 0 0.03 0 0.04 0 0.03 0 0.04 0 0.03 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.05 0 0.06 0 0.05 0 0.05 0 0.05 0 0.06 0 0.05 0 0.06 0 0.05 0 0.07 -0.001441 0.07 0 0.07 0 0.07 0 0.07 -0.001395 0.07 0 0.07 0 0.07 0 0.07 0 0.06 0 0.07 0 0.07 0 0.08 0 0.07 0 0.07 0 0.07 0 0.09 -0.001441 0.09 0 0.09 0 0.09 0 0.09 -0.001395 0.08 0 0.09 0 0.09 0 0.09 0 0.08 0.0013491 0.08 0 0.09 0 0.09 0 0.08 0 0.09 0 0.08 0 0.11 -0.001441 0.1 0 0.1 0 0.11 0 0.1 -0.001395 0.1 0 0.11 0 0.1 0 0.11 0 0.1 0.0013491 0.1 -0.0013611 0.1 0 0.11 0 0.1 0 0.11 0 0.1 0 0.12 -0.001441 0.12 0 0.12 0 0.12 0 0.12 -0.001395 0.12 0 0.12 0 0.12 0 0.13 0 0.12 0.0013491 0.12 -0.0013611 0.12 0 0.13 0 0.12 0 0.12 0 0.12 0
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))
pct = 1×16
10 20 30 40 50 60 70 80 70 60 50 40 30 20 10 0

1 commentaire
Afficher -1 commentaires plus anciens Masquer -1 commentaires plus anciens

Robin Nilsson
Robin Nilsson le 17 Oct 2022
Thank you for the very detailed help. I noticed that my columns for the different data columns are different length which causes allot of empty NaN which was what I was observing along with the text NaN in the first rows.

Connectez-vous pour commenter.

Plus de réponses (1)

Kevin Holly
Kevin Holly le 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 commentaires
Afficher 1 commentaire plus ancien Masquer 1 commentaire plus ancien

Robin Nilsson
Robin Nilsson le 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
Robin Nilsson le 17 Oct 2022
Thank you, I realised that I have many NaN due to some of the columns being much longer than the others.
dpb
dpb le 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.

Connectez-vous pour commenter.

Catégories

En savoir plus sur Spreadsheets dans Centre d'aide et File Exchange

Produits

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by