CODEWITHSUNDEEP
excelmatlab
Sham
Sham

Reputation: 21

How to remove the entire row with values missing only in some columns?

I need to remove all rows with empty columns in excel file using matlab programming. I have multiple worksheets inside a single excel file and the data are in a format as follows:

1 2 3 4 5
2 3 4 5 
5 6 7 8 9
12 13 14 15 16
6 2 8 4
1 2 3 5 7
9 8 34 3
3 2 7 8 4

In the above example, I have empty cells in rows 2, 5, and 7 (all empty cells in column 5). I need to find these empty values and remove rows containing these values i.e. I need to remove only rows 2, 5 and 7. The final output should be like below in an excel file

1 2 3 4 5
5 6 7 8 9
12 13 14 15 16
1 2 3 5 7
3 2 7 8 4

I looked at some solutions, where rows with all zeros are removed but in this case, I have empty cells only in column 5 but I need to remove the row containing empty values. Any help is appreciated.

Upvotes: 2

Views: 4041

Answers (3)

yuk
yuk

Reputation: 19880

Here is how you can detect and remove rows with empty cells using ActiveX directly:

e = actxserver ('Excel.Application'); %# open Activex server
ewb = e.Workbooks.Open('c:\test\test.xlsx'); %# open file (enter full path)
eur = ewb.ActiveSheet.UsedRange; %# lets simplify using active sheet
data = cell2mat(eur.Value); %# get numeric data
idx = find(any(isnan(data),2))'; %# find rows with empty (or text) cells
for k=idx(end:-1:1)
    eur.Rows.Item(k).Delete; %# delete entire row from the last one
end
ewb.Save %# save to the same file
ewb.Close(false)
e.Quit

It saves the same altered file so make sure you have a backup while testing!!!

Upvotes: 1

gnovice
gnovice

Reputation: 125874

If you are using XLSREAD to read in your data, it will automatically pad empty cells with NaN values. You can therefore use the functions ANY and ISNAN to remove the rows containing NaN values and save the new data to a file using XLSWRITE, like so:

data = xlsread('oldfile.xls');
xlswrite('newfile.xls', data(~any(isnan(data), 2), :));

Upvotes: 2

zamazalotta
zamazalotta

Reputation: 433

Since you data matrix will be of a dimension (8,5) all the empty cells will be 0. So you can do

min_data=min(data,[],2); %find the minimum of the rows
data(find(min_data==0),:)=[]; %find the rows of zeros and delete them

Upvotes: 1

Related Questions