Why are there a few bytes added every time I call writetable?

2 views (last 30 days)
I have some tests that require updating some fields of an excel file, but every time I save it, it adds a few bytes of data. Opening the file with microsoft excel doesn't show any additional data and saving it (without doing anything else) purges all the extra bytes added. This makes me think that maybe writetable adds some metadata.
My main issue there, is that after a bunch of cycles the file gets corrupted and the code crashes when attempting to re-load the .xlsx file. Running this on my computer results in about 6 bytes added, on another one it is about 19, while in this description code, it adds about 3 bytes.
filename = "test.xlsx";
% Create a table with dummy data
ta = array2table(rand(3,3));
writetable(ta,filename,'WriteVariableNames',false);
%% Do many xlsread/writetable cycles
N = 100;
fileLength(N) = 0;
figure();
al = animatedline('linestyle','-','marker','.','color','k');
xlabel('Save cycle')
ylabel('File size [bytes]')
for i = 1:N
% read file (to a cell array)
[~, ~, data] = xlsread(filename, 1, '');
data(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),data)) = {''};
% Convert to table and writetable
ta = data;
writetable(table(ta),filename,'WriteVariableNames',false);
pause(0.01);
% Check the file size (I was using .NET's fileinfo, but had to switch
% to matlab's dir for this code example to run in the browser.
% temp = System.IO.FileInfo(filename);
% fileLength(i) = temp.Length;
temp = dir(filename);
fileLength(i) = temp.bytes;
addpoints(al, i, fileLength(i));
end
%% Polyfit to get the increase in file size
x = 1:i-1;
y = fileLength(1:i-1);
p = polyfit(x,y,1);
fprintf("File increases by %.1f bytes on average\n",p(1));
File increases by 3.1 bytes on average

Accepted Answer

Jeremy Hughes
Jeremy Hughes on 25 Aug 2021
Edited: Jeremy Hughes on 25 Aug 2021
writetable write "into" a sheet by default. And there's some meta-data being added, but I'm not sure if it's MATLAB, or the excel format. I used the "replacefile" option, and that made the graph flat.... but that might not be what you want to use in general. I don't know if you intend to write in-place, or not.
If your real work requires the file to be written in place to different sheets, this might not resolve the issue.
I'll look into it some more. (Edited for code brevity)
filename = "test.xlsx";
% Create a table with dummy data
ta = array2table(rand(3,3));
writetable(ta,filename,'WriteVariableNames',false);
%% Do many xlsread/writetable cycles
N = 100;
fileLength(N) = 0;
figure();
al = animatedline('linestyle','-','marker','.','color','k');
xlabel('Save cycle')
ylabel('File size [bytes]')
for i = 1:N
% read file (to a cell array)
data = readtable(filename);
% Convert to table and writetable
writetable(data,filename,'WriteVariableNames',false,"WriteMode","replacefile");
temp = dir(filename);
fileLength(i) = temp.bytes;
addpoints(al, i, fileLength(i));
end
  2 Comments
Martin Caron
Martin Caron on 25 Aug 2021
Thanks! I tried using the "replacefile" option, but it seems to be unavailable in my version of matlab. In the mean time, I will switch to the "UseExcel". Even though it adds coupling to an external software, we tend to have it installed on every computer where I work.

Sign in to comment.

More Answers (0)

Products


Release

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by