How do I dynamically remove specific rows from an excel sheet?

I am currently working on an app which downloads and processes some data from a server. The data is encoded with hashStrings and to keep track of the present files, some specific identifiers of the encoded data are written out into a log file, which is an .xlsx sheet. I want the log file to dynamically update whenever the app is started and remove rows from my log file whever a data file has been removed from the respective folder. my approach was to compare the hash strings in my log files to the hash strings of my .mat (data) files and remove these rows if no match can be found. This does also work pretty well untill i want to overwrite the already existing log file. Whatever i try, i always get the correct "updatedLog" in my workspace but not an actually updated log file in the respective directory. I would be grateful about any insights as to why my approach is not doing what i need it to do.

function updateLogFile(app)
            % load the existing log file
            logFilePath = app.logFile;
            % get he encoded .mat files path
            inputDir = fullfile(app.dataFolder, '\Input');
            % read log file table and convert entries from "cell" to
            % "string" for comparability
            currentLogFile = readtable(logFilePath);
            currentLogFile.HashStr = string(currentLogFile.HashStr);
            % search for the .mat files in question and extract the
            % hashstring from the file name. Then put the hashstrings into
            % a table for better compatability.
            inputFileList = dir(fullfile(inputDir, 'container_*.mat'));
            fileHash = cellfun(@(x) regexp(x, '(?<=_)[a-f0-9]+(?=\.mat)', 'match',...
                         'once'), {inputFileList.name}, 'UniformOutput', false);
            fileHash = table(string(fileHash(:)), 'VariableNames', {'HashStr'});
            % compare the log file entries with the found file name hash
            % strings
            hashIsPresent = ismember(currentLogFile.HashStr, fileHash.HashStr);
            % only write out the matched rows
            updateLog = currentLogFile(hashIsPresent, :);
            % write out table "overwrite existing .xlsx file" and recieve the same blooming table i started
            % with... >:(
            writetable(updateLog, app.logFile);
        end

[Image Analyst]

It would help if you had attached a workbook along with the row numbers that you wish to delete.

If you have any more questions, then attach your data and code to read it in with the paperclip icon after you read this:

TUTORIAL: How to ask a question (on Answers) and get a fast answer

[dpb]

            hashIsPresent = ismember(currentLogFile.HashStr, fileHash.HashStr);
            % only write out the matched rows
            updateLog = currentLogFile(hashIsPresent, :);

If you're trying to remove those not present, shouldn't you be keeping the difference between who's in the directory list and the present hash list instead?

[Eva]

Thanks @Image Analyst, this is very helpful. I am fairly new to Matlab and hence also not too familiar with the community and how to properly ask questions. This will be very helpful for any further questions i might have!

[Eva]

@dpb I'm not sure wether I fully understand what you mean. But what the updateLog is doing is making an logical index which is applied to the existing LogFile. So positions with a 0 entry as logical index should be cancelled out and positions with 1 are to stay. I thought this could forego the need for an "intermediate" file where the difference is written into.

[dpb]

It looked to me like there was a good chance the reason the file wasn't changing is because what you were writing was the same as what you read...but, I didn't actually try to debug without any data.

I've never had an instance in which the various versions didn't write what was requested even after reading and munging on the same file so I'm very suspicious it's something else going on.

I was just reminded of the one case in which that might happen is that writetable and friends will NOT accept an absolute Excel address (one with the "$" signs) if the Range parameter is given -- in that case it does just silently fail to write anything. I don't recall if I have previously submitted this as a bug/implementation issue or not but am going to after this last debugging session...

Answer 1

[Hitesh]

Hi @Eva,

To ensure that the updated log file is written correctly, you need to first write the updated data to a new temporary Excel file and then replace the old log file with this new file. This approach can help avoid issues related to file locks or access permissions. Kindly refer to the below code for updating log file :

function updateLogFile()
    % Define paths
    logFilePath = fullfile('project', 'Logs', 'log.xlsx');
    tempLogFilePath = fullfile('project', 'Logs', 'temp_log.xlsx');
    inputDir = fullfile('project', 'Input');

    % Read log file table
    currentLogFile = readtable(logFilePath);
    currentLogFile.HashStr = string(currentLogFile.HashStr);

    % Get .mat files and extract hash strings
    inputFileList = dir(fullfile(inputDir, 'container_*.mat'));
    fileHash = cellfun(@(x) regexp(x, '(?<=_)[a-f0-9]+(?=\.mat)', 'match', 'once'), ...
                       {inputFileList.name}, 'UniformOutput', false);
    fileHash = string(fileHash(~cellfun('isempty', fileHash)));

    % Filter log entries to include only those with matching files
    matchingEntries = ismember(currentLogFile.HashStr, fileHash);
    updatedLog = currentLogFile(matchingEntries, :);

    % Remove duplicate entries
    updatedLog = unique(updatedLog, 'rows');

    % Debug: Display the updated log
    disp('Updated Log:');
    disp(updatedLog);

    % Try to write the updated log file to a temporary file
    try
        writetable(updatedLog, tempLogFilePath);
        disp('Temporary log file written successfully.');

        % Replace the old log file with the new one
        movefile(tempLogFilePath, logFilePath);
        disp('Log file updated successfully.');
    catch ME
        warning('Failed to write the log file: %s', ME.message);
        % Clean up temp file if something goes wrong
        if exist(tempLogFilePath, 'file')
            delete(tempLogFilePath);
        end
    end
end

% Run the update function
updateLogFile();

[Eva]

Thank you so much! Works like a charm :)