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

11 ビュー (過去 30 日間)
Eva
Eva 2024 年 12 月 23 日
コメント済み: dpb 2024 年 12 月 29 日
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
  5 件のコメント
Eva
Eva 2024 年 12 月 27 日
@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
dpb 2024 年 12 月 29 日
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...

サインインしてコメントする。

採用された回答

Hitesh
Hitesh 2024 年 12 月 24 日
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();
  1 件のコメント
Eva
Eva 2024 年 12 月 27 日
Thank you so much! Works like a charm :)

サインインしてコメントする。

その他の回答 (0 件)

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by