xlsread() not reading entire values in column
28 views (last 30 days)
Show older comments
Jimmy Mejia
on 21 Dec 2021
Commented: Jimmy Mejia
on 27 Dec 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 Comments
Accepted Answer
Cris LaPierre
on 23 Dec 2021
Edited: Cris LaPierre
on 23 Dec 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)
More Answers (1)
Voss
on 23 Dec 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 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!