xlsread() not reading entire values in column

Question

Jimmy Mejia 2021-12-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1615540-xlsread-not-reading-entire-values-in-column

评论： Jimmy Mejia 2021-12-27

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 个评论
显示无隐藏无

Geoff Hayes 2021-12-21

@Jimmy Mejia - can you attach the data file?

Jimmy Mejia 2021-12-23

Boards_Status.xlsx

Here you go. Thank you

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Cris LaPierre 2021-12-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1615540-xlsread-not-reading-entire-values-in-column#answer_860860

编辑：Cris LaPierre 2021-12-23

在 MATLAB Online 中打开

Boards_Status.xlsx

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.

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.

Then all the numbers imported correctly.

[status, sheet] = xlsfinfo('Boards_Status.xlsx');
boardShippedList = xlsread('Boards_Status.xlsx',sheet{1},'B:B');
boardShippedList(53:65)
ans = 13×1
    51200033
    51200066
    51200061
    51200064
    51200158
    51200144
    51200149
    51200137
    51200184
    51200159

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Jimmy Mejia 2021-12-27

Screenshot 2021-12-27 122038.png

Thank you! This solved my problem. I did notice the formats were wrong early on and changed it but MATLAB would still not recognize the change. For whatever reason, the visible warning sign don't show up on my spreadsheet so I ran the error checking feature and changed the cell format using that. I ran the MATLAB code and it worked.

请先登录，再进行评论。

Answer 2

Voss 2021-12-23

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1615540-xlsread-not-reading-entire-values-in-column#answer_860975

在 MATLAB Online 中打开

Boards_Status.xlsx

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,:));

11×1 cell array {[51200055]} {[51200067]} {[51200069]} {[51200033]} {'51200066'} {[51200061]} {'51200064'} {'51200158'} {'51200144'} {'51200149'} {'51200137'}

idx = cellfun(@(x)ischar(x),data);

data(idx) = cellfun(@(x)str2double(x),data(idx),'UniformOutput',false);

data = cell2mat(data);

display(data(50:60,:));

51200055 51200067 51200069 51200033 51200066 51200061 51200064 51200158 51200144 51200149 51200137

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。