how to delete the empty row in the excel with matlab

hi, as the title description, i want use matlab to delete the empty rows in the excel which has many rows, how to realize.

Answer 1

[Neelanshu]

Hi Wenchao,

You can delete the empty rows from an excel sheet by writing the post-processed data into an .XLSX file. You can identify the missing rows with the help of the following functions :

'ismissing' : it returns a logical array indicating the missing entries.

'all' : it determines if all the elements of the array are nonzero

% Step 1: Read the Excel file into MATLAB
filename = 'filename.xlsx'; % Replace with your file name
sheetName = 'Sheet1'; % Specify the sheet name if necessary
data = readtable(filename, 'Sheet', sheetName);

% Step 2: Identify and remove empty rows
emptyRows = all(ismissing(data), 2);
data_cleaned = data(~emptyRows, :);

% Step 3: Write the cleaned data back to the Excel file
writetable(data_cleaned, 'cleaned_file.xlsx', 'Sheet', sheetName);

You may refer to the following documentation to learn more about the functions 'ismissing' and 'all' :

https://www.mathworks.com/help/matlab/ref/all.html

https://www.mathworks.com/help/matlab/ref/ismissing.html

Hope this helps!

[wenchao zhang]

hi,Neelanshu, thaks for your respond, yes it works, but i update the script as following shows:

% Step 1: Read the Excel file into MATLAB

filename = 'dataImport.xlsx'; % Replace with your file name

sheetName = 'Sheet1'; % Specify the sheet name if necessary

data = readtable(filename, 'Sheet', sheetName);

% Step 2: Identify and remove empty rows

emptyRows = all(ismissing(data), 2);

data_cleaned = data(~emptyRows, :);

data_cleaned = table2cell(data_cleaned);

% Step 3: Write the cleaned data back to the Excel file

xlswrite('dataImport_new.xlsx',data_cleaned);

[wenchao zhang]

A = [3 NaN 5 6 7 NaN NaN 9];

TF = ismissing(A)

TF = 1x8 logical array

0 1 0 0 0 1 1 0

[wenchao zhang]

% Step 1: Read the Excel file into MATLAB

[filename, pathname] = uigetfile( ...

{'*.xlsx';'*.*'}, ...

'Pick a file');

[filepath,name,ext] = fileparts(filename);

sheetName = 'Sheet1'; % Specify the sheet name if necessary

data = readtable(filename, 'Sheet', sheetName);

% Step 2: Identify and remove empty rows

emptyRows = all(ismissing(data), 2);

data_cleaned = data(~emptyRows, :);

data_cleaned = table2cell(data_cleaned);

% Step 3: Write the cleaned data back to the Excel file

xlswrite([name,'_new.xlsx'],data_cleaned);

[wenchao zhang]

d = dialog('Position',[300 300 250 150],'Name','My Dialog');

txt = uicontrol('Parent',d,...

'Style','text',...

'Position',[20 80 210 40],...

'String','Clear All Value?');

btn = uicontrol('Parent',d,...

'Position',[85 20 70 25],...

'String','Yes',...

'Callback','delete(gcf)');

uiwait(d);

clc;

clear;

[filename, pathname] = uigetfile( ...

{'*.xlsx';'*.*'}, ...

'Pick a file');

[filepath,name,ext] = fileparts(filename);

sheetName = 'Sheet1'; % Specify the sheet name if necessary

data = readtable(filename, 'Sheet', sheetName);

% Step 2: Identify and remove empty rows

emptyRows = all(ismissing(data), 2);

data_cleaned = data(~emptyRows, :);

data_cleaned = table2cell(data_cleaned);

% Step 3: Write the cleaned data back to the Excel file

xlswrite([name,'_new.xlsx'],data_cleaned);

Answer 2

[Sreeram]

Hi Wenchao,

You can make use of the MATLAB function “cellfun” for such operations. You may read about it here:

https://www.mathworks.com/help/matlab/ref/cellfun.html

If you want to delete all empty rows of an excel file, the command will be:

C(all(cellfun('isempty',C),2),:) = []; 

where C is the input array which you can get by reading the Excel file.

Hope it helps.

[wenchao zhang]

hi，Sreeram, thanks, i have tried it, seems failed

[Sreeram]

Thanks for informing me, Wenchao.

If you had used the "readTable" function to read the file, it might not have worked because of a mismatch in variable type of C passed to the "cellfun". The "readTable" detects the data type to determine how to import the data. Because the data in dataImport.xlsx was only numbers, the "readTable" might have set empty cells to NaN, and so the "isempty" check was insufficient.

It can be fixed by following the instructions given here to set the type to char. Here is how you can implement it:

opts = detectImportOptions('dataImport.xlsx');
opts = setvartype(opts, 'char');
data = readtable('dataImport.xlsx', opts);
data = table2cell(data);

data(all(cellfun('isempty',data),2),:) = [];

Hope this helps..

[wenchao zhang]

ok,thanks,Sreeram,i will try it, i think it can works finally.

Answer 3

[Romain]

Hi,

Not sure about what you mean, do you want to:

1. Delete a row that contain some empty cells (but the row still has non-empty cells) or,
2. Delete the rows that contain only empty cells ?

Anyway,

In case 1: add MissingRule="omitrow" in the readtable function. Doing that will delete all rows that contain 1 or more empty cell.

In case 2: Should you have row name, add ReadRowNames = true in the readtable function. Otherwise, the readtable function automatically gets rid of empty rows.

[wenchao zhang]

hi,romain,my case is delete all the rows that contains only empty cells