how to delete the empty row in the excel with matlab

Question

wenchao zhang 2024-8-16，5:44

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2145664-how-to-delete-the-empty-row-in-the-excel-with-matlab

评论： wenchao zhang 2024-8-16，14:28

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

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

请先登录，再进行评论。

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

Answer 1

Neelanshu 2024-8-16，7:05

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2145664-how-to-delete-the-empty-row-in-the-excel-with-matlab#answer_1499594

在 MATLAB Online 中打开

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!

4 个评论
显示 2更早的评论隐藏 2更早的评论

wenchao zhang 2024-8-16，9:34

% 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 2024-8-16，9:57

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 2024-8-16，6:58

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2145664-how-to-delete-the-empty-row-in-the-excel-with-matlab#answer_1499584

在 MATLAB Online 中打开

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.

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

Sreeram 2024-8-16，10:19

在 MATLAB Online 中打开

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 2024-8-16，14:21

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

请先登录，再进行评论。

Answer 3

Romain 2024-8-16，7:12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2145664-how-to-delete-the-empty-row-in-the-excel-with-matlab#answer_1499604

编辑：Romain 2024-8-16，7:12

Hi,

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

Delete a row that contain some empty cells (but the row still has non-empty cells) or,
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.