writetable does not replace file
92 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Dominik
el 7 de En. de 2019
Comentada: Ron Fredericks
el 2 de Feb. de 2022
Hi,
I just observed a somewhat confusing behaviour with the writetable function in matlab. Here is a simple code to explain it
t = T(1:10,:)
writetable(t, '/tmp/test.xlsx')
writetable(t, '/tmp/test1.xlsx')
t = t([2 4 1 3 5],:)
writetable(t, '/tmp/test.xlsx')
writetable(t, '/tmp/test2.xlsx')
now test2.xlsx is a table with 5 rows, as expected.
test.xlsx is a table with 10 rows, wheres the first 5 equal test2.xlsx and the last 5 equal test1.xlsx
I would expect test.xlsx should be the same as test2.xlsx. I had a misunderstanding in a collaboration with colleagues which was close to publish erroneous data.
I there a way to change this?
I'm using matlab on ubuntu 18.04 with libreoffice.
Thanks for any help
Dom
2 comentarios
Stephen23
el 7 de En. de 2019
Editada: Stephen23
el 7 de En. de 2019
"...table.xlsx is a table..."
Your code does not write any file named table.xlsx
"table.xlsx is a table with 10 rows, wheres the first 5 equal test2.xlsx and the last 5 equal test1.xlsx"
As far as I can tell test2.xlsx will have five rows and test2.xlsx will have ten rows. How do you expect ten rows plus five rows to equal ten rows?
"I would expect table.xlsx should be the same as table2.xlsx"
Your code does not write any file named table2.xlsx
Please ensure your question is consistent so that we can understand what you are doing and what you expect to happen.
Respuesta aceptada
Stephen23
el 7 de En. de 2019
Editada: Stephen23
el 7 de En. de 2019
According to the writetable help, "If filename is the name of an existing spreadsheet file, then writetable writes a table to the specified location, but does not overwrite any values outside that range", and that is exactly what you are observing:
- write ten values to a (new?) spreadsheet.
- write five values to a (now already existing) spreadsheet. According to the documentation this will add those five values to the default location, as in your code and leave all other existing values unchanged.
- thus you still see five of the values from step 1. (the ones not overwritten by step 2.), and five values from step 2..
So far it seems to be behaving as described in the documentation.
While this might be useful in some situations clearly in other cases (like yours) it would be useful to delete any existing content before writing the new content, but there does not currently seem to be an option for this. I suggest that you make an enhancement request (with a link to this thread), as I am sure that others would also find this a useful option.
3 comentarios
Philip Borghesani
el 7 de En. de 2019
(this comment double posted in both answers)
Interesting, I entered a bug report for the documentation about this, I think it should be spelled out more explicity in the help as well as being bolded in the doc.
The enhancment to add an overwite mode exists already in our system. Feel free to contact support and add a bit more push for an enhancment and I will push a bit.
Antoine Skaf
el 14 de Oct. de 2020
FYI, you can set the 'WriteMode' option to 'overwritesheet' for WRITETABLE when writing to an Excel file as of R2020a. From the Doc:
'overwritesheet' — Clear the specified sheet and write the input data to the cleared sheet.
- If you do not specify a sheet, then the writing function clears the first sheet and writes the input data to it.
Más respuestas (1)
Dominik
el 7 de En. de 2019
Editada: Dominik
el 7 de En. de 2019
9 comentarios
Les Beckham
el 1 de Feb. de 2022
I agree with Walter's comments. However, I agree with OP and with Ron Fredricks that the message displayed by uiputfile() is misleading/confusing.
Perhaps if the message was "do you want to select this existing file" instead of "do you want to replace this file" it would make more sense, since what uiputfile() does is just select a file and return its name. It never "replaces" any file.
In fact, the name of this function is, in itself, pretty misleading as it doesn't ever "put" any file anywhere.
Obviously this is confusing and should be clarified either in the documentation or by changing the message displayed by the function itself.
My 2 cents.
Ron Fredericks
el 2 de Feb. de 2022
Thank you all for looking into my uiputfile with excel's writematrix (or writetable) function. I have taken note of Walter's comments and rewrote my code to avoid this issue. Yet I also agree with Les that the wording (and even the name itsefl) for uiputfile needs a little work to avoid confusion for new users.
My updated code...
investigator = 'dummy value for this test code';
filter = {'*.xlsx'};
[filename,filepath] = uiputfile(filter);
if filename==0
% User aborted or canceled uiputfile, so just return without warning
xWarning = '';
return
end
% Test for requested file to save already exists and is currently open.
if isfile(filename)
[fid, ~] = fopen([filepath filename],'a');
if fid==-1
% Issue warning to user that excel file to be replaced is open,
% and therefore can not be replaced.
xWarning = filename;
return
else
xWarning = '';
fclose(fid);
end
end
lineNum = 1;
infoCol = 'B';
% First use of writematrix includes request to replace file if it
% exists.
writematrix(investigator,[filepath filename],'Sheet',1, ...
'Range',[infoCol num2str(lineNum)],'WriteMode', 'replacefile')
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!