xlsread() not reading entire values in column
28 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
Jimmy Mejia
il 21 Dic 2021
Commentato: Jimmy Mejia
il 27 Dic 2021
The purpose of my script is to read the valules of an entire colum (except the label) and append those values to a string that is a directory. example: /C:\Users\<name>\Documents\board_<value of cell in the column>
I then access information using the directory with the appended value using a for loop.
My problem is that when I run the xlsread() command, the function doesn't read the entire column. It instead reads most of the cells in the column and other cells the function can't read and yeilds a NaN (Not a Number) (see attached).
The code is simple: the first line of code i use to visualy see that the correct worksheet is seen. The second reads the worksheet and the column B in that worksheet.
[status, sheet] = xlsfinfo('...\<folder>\spreadsheet.xlsx')
boardShippedList = xlsread('...\<folder>\spreadsheet.xlsx','worksheet','B:B')
I have tried rearranging the values in the spreadsheet (snippet attached) and defined the exact cells to read, B2:B65 as an example but still haven't been sucessful.
I understand xlsread() isn't recommneded anymore it was working well in another spreadsheet with different data. Can someone assist with this issue.
Greatly appreciated!
2 Commenti
Risposta accettata
Cris LaPierre
il 23 Dic 2021
Modificato: Cris LaPierre
il 23 Dic 2021
When opened in Excel, notice that the cells that are being read in as NaN have a green triangle in the upper left. The warning message is that these cells are numbers formatted as text.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/842150/image.png)
This is why they are appearing as NaN in MATLAB. They are text, not numbers.
This is not excel's formatting of the cell - that is set to General, the same as all the other numbers. Changing this had no effect. Instead, I had to highlight the cell and then click on the warning icon to get to option to convert the text to numbers.
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/842155/image.png)
Then all the numbers imported correctly.
[status, sheet] = xlsfinfo('Boards_Status.xlsx');
boardShippedList = xlsread('Boards_Status.xlsx',sheet{1},'B:B');
boardShippedList(53:65)
Più risposte (1)
Voss
il 23 Dic 2021
As an alternative to @Cris LaPierre's answer, for instance If manipulating the file(s) in Excel is not feasible because, say, there are a large number of files with this problem you have to deal with, you can handle this in MATLAB by loading in the data as a cell array and manipulating the cell array:
[~,~,data] = xlsread('Boards_Status.xlsx',1,'B:B');
data(1,:) = []; % remove the header line
display(data(50:60,:));
idx = cellfun(@(x)ischar(x),data);
data(idx) = cellfun(@(x)str2double(x),data(idx),'UniformOutput',false);
data = cell2mat(data);
display(data(50:60,:));
0 Commenti
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!