how to delete a rows with column zeros in excel file

Please find the attached excel sheet. In that we have a column from "A to J". out of these i will focus only in column 'C' having zeros in start / end.

My doubt is How to delete all start and end rows with respect to zeros present in column 'C' . suppose column 'C' having zeros in starting and ending, so i need to delete all corresponding rows and saves a new excel file in another location path.

Kindly give a suggestion on this !

here i attached a file "output RPM' and i need like this.

Note:

refer "output RPM" file, In that column 'C', having one zero in start and end of the rows and the remaining rows will be deleted. like this i want . kindly help me

[file,filepath]=uigetfile({'*.csv;*.xlsx;*.xls';},'Select Trajectory Table File(s)','C:\Users\INARUPAR\Desktop\MATLAB WORKOUT\', 'MultiSelect', 'on');

if ~iscell(file)

file = {file};

disp(file)

end

output_file_name = 'output RPM.csv';

column_name = {'Y1'}; % <--- columns name here

fig = figure();

ax = axes();

l = legend(ax);

hold(ax);

ax.ColorOrder = [1 0 0 ; 0 0 1]; % it gives RED Color

drawnow;

for i=1:numel(file)

t = readtable([filepath file{i}],"VariableNamingRule","preserve"); % Read all Data from Selected Excel Sheet

writetable(t(:, column_name),'C:\Users\INARUPAR\Desktop\MATLAB WORKOUT\Check.xlsx' , 'Range', [char(65+numel(column_name)*(i-1)) '1']);

p = plot(t{:, column_name});

l.String(end-numel(column_name)+1:end) = strcat(column_name, '-', num2str(i));

drawnow;

end

Answer 1

[Manish]

Hi,

I understand that you want to clean your CSV file by eliminating all the rows with zeros in column C (keep the first and last row) and save it.

One way to implement the above task is with the code below:

% Set output directory and filename pattern
outputDir = 'C:\Users\output_files';
outputFilenamePattern = 'output_RPM_%d.csv';
[file, filepath] = uigetfile({'*.csv;*.xlsx;*.xls'}, 'Select Trajectory Table File(s)', 'C:\Users\INARUPAR\Desktop\MATLAB WORKOUT\', 'MultiSelect', 'on');

if ~iscell(file)
    file = {file};
end

for i = 1:numel(file)
    fullFilePath = fullfile(filepath, file{i});
    % Step 1: Load the entire file as text
    fileID = fopen(fullFilePath, 'r');
    fileContent = textscan(fileID, '%s', 'Delimiter', '\n', 'Whitespace', '');
    fclose(fileID);
    
    % Convert the cell array to a string array
    fileLines = string(fileContent{1});
    
    % Step 2: Extract the data columns into a table
    % Find the line where the data starts
    dataStartLine = find(contains(fileLines, 'X,X [ms],Y1,Y2,Y3,Y4,Y5,Y6,Y7,Y8'), 1);
    
    % Extract the data lines
    dataLines = fileLines(dataStartLine:end);
    
    % Write these lines to a temporary CSV file
    tempFileName = 'temp_data.csv';
    fileID = fopen(tempFileName, 'w');
    fprintf(fileID, '%s\n', dataLines);
    fclose(fileID);
    
    % Read the data into a table
    opts = detectImportOptions(tempFileName);
    opts.SelectedVariableNames = {'X', 'X_ms_', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Y6', 'Y7', 'Y8'};
    data = readtable(tempFileName, opts);
    
    % Step 3: Remove leading zeros from Y1
    firstNonZeroIndex = find(data.Y1 ~= 0, 1, 'first');
    data = data(firstNonZeroIndex-1:end, :);
    
    % Step 4: Remove trailing zeros from Y1
    lastNonZeroIndex = find(data.Y1 ~= 0, 1, 'last');
    data = data(1:lastNonZeroIndex+1, :);
    
    % Step 5: Combine metadata and data into a new file
    outputFileName = fullfile(outputDir, sprintf(outputFilenamePattern, i));
    
    % Write the metadata (all lines before the data start line)
    fileID = fopen(outputFileName, 'w');
    fprintf(fileID, '%s\n', fileLines(1:dataStartLine-2));
    fclose(fileID);
    
    % Append the cleaned data to the new file
    writetable(data, outputFileName, 'WriteVariableNames', true, 'WriteMode', 'append');
    
    % Optionally, delete the temporary file
    delete(tempFileName);
  
    disp(['Data processing complete for file: ', file{i}, '. Output saved to ', outputFileName]);
end

The code processes selected trajectory data files by extracting data, removing rows with zeros in column C, and saving the cleaned data with metadata to a new output file in a specified directory.

You can change the output directory by modifying the “outputDir” variable, and the output file name can also be adjusted using the “outputFilenamePattern” variable.

Hope it helps!