How to prevent .xlsread to turn string data to NaN value

Hi there,

I got this code at the moment. I converted my file to .xlsx so I can read my data in matlab. What the forloop does is it goes through the data and goes through cells that contains NaN value then deletes the entire row, resulting in no NaN values in the data set. However, in the end the columns that contained 'string' values turns to NaN value. How do I convert back those NaN values to string values again?

% Reads as xlsx file and contains a forloop that deletes rows that contains
% NaN value - However, when xlsxread is used, the columns that contains strings turns to NaN value. 
T = xlsread('testfile4.xlsx');
for col = 6:size(T,2)
   i = find(isnan(T(:,col)));  %find all row with “NaN” in this col
   T(i ,:) = [];
end

This is what it looks like after deleting the rows with NaN values. Now the second and third columns contains NaN values which was strings before.

[Andrea Del Rosario]

Attached the file that I used for NaN rows deletion. PLEASE HELP :(

Answer 1

[Shubham Gupta]

MATLAB doesn't give the option to store 'string' and 'double' in an double array. You can read the excel file and store it in a 'cell' array. You can try this :

[number,string,everything]=xlsread('testfile4.xlsx');
for col=5:2:size(everything,2)
    rind=find(number(:,col)==0);
    everything(rind+1,:)=[]; % '+1' because there are names in 1st row
end

I hope this helps

[Andrea Del Rosario]

Hi there Shubham,

is there a way to specify the only columns needed using this method? What I mean is, looking at the .xlsx file I attached, is there a way to just get 'PcbTestID, ' Verify12VInput' , 'VerifyCurrentDraw'?

[Walter Roberson]

You can select columns out of the everything variable.

You can also consider using:

number( :, all(isnan(number),1) ) = [];

which is to say you can delete the columns that are entirely nan. (This is not foolproof, as the reading routine cannot tell the difference between a number and the same number as a string. So for example an entry of 123 and an entry of '123' would both be interpreted as numeric.)

[Andrea Del Rosario]

Hi Walter,

in relation to what you said 'You can select columns out of the everything variable.; I tried creating another variable to specify the columns I wanted to show then do the deletion of rows, however I had error saying :

[number,string,everything]=xlsread('testfile4.xlsx');
%New variable added to choose specific columns
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
for col=5:2:size(PCBRequiredCol,2)
    rind=find(number(:,col)==0);
    PCBRequiredCol(rind+1,:)=[]; % '+1' because there are names in 1st row  
end

%Here is the error when I made changes
>>Unable to use a value of type 'cell' as an index.
>>Error in attempt4 (line 12)
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
 

Is there another way to specify columns using the everything variable?

[Walter Roberson]

You cannot index a cell array by column name, only by column number. You can index a table by column name:

everything = readtable('testfile4.xlsx');
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});

The result would be a table object with those columns. The PcbID column is character so you cannot just convert this to numeric without excluding that column

num = PCBRequiredCol{:, {'PcbTestID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'}};

[Andrea Del Rosario]

That make sense, but where do I plug this in with my code at the moment? Would you please be able to show me? This is what I have at the moment :(

[number,string,everything]=xlsread('testfile4.xlsx');
%New variable added to choose specific columns
PCBRequiredCol = everything(:,{'PcbTestID','PcbID','Verify12VInput','VerifyCurrentDraw','RailNve5V','Rail3V3'});
for col=5:2:size(PCBRequiredCol,2)
    rind=find(number(:,col)==0);
    PCBRequiredCol(rind+1,:)=[]; % '+1' because there are names in 1st row  
end

Answer 2

[Cris LaPierre]

xlsread with a single output only returns numeric data.

• num = xlsread(filename) reads the first worksheet in the Microsoft® Excel® spreadsheet workbook named filename and returns the numeric data in a matrix.

If you want to capture other data types, you have to call it with more output variables.

[num,txt,raw] = xlsread(___)

The variable raw will contain all the data. Look at the documentation page for the details.

Answer 3

[Walter Roberson]

Use readtable() . If you are using R2016b or later, you can use rmmissing() to remove the table rows with missing entries.