xlsread() not reading entire values in column

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!

[Geoff Hayes]

@Jimmy Mejia - can you attach the data file?

[Jimmy Mejia]

Here you go. Thank you

Answer 1

[Cris LaPierre]

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

[Jimmy Mejia]

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]

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