writetable does not replace file
显示 更早的评论
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 个评论
"...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.
采纳的回答
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 个评论
Dominik
2019-1-7
Philip Borghesani
2019-1-7
(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
2020-10-14
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.
更多回答（1 个）
9 个评论
Stephen23
2019-1-7
"...however the help writetable is misleading to me"
I had to read it twice too, but it seems that the phrase "writetable overwrites any existing file." only applies to the syntax writetable(T) (which it is listed under) which creates a text file, and not to any other syntaxes (i.e. those where the filename and filetype are specified). As far as I can tell, the Mfile help does not specify anything about what happens to preexisting spreadsheet data.
Philip Borghesani
2019-1-7
Interesting, I entered a bug report for the documentation about this, I think it should be spelled out more explicity too. 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.
Ron Fredericks
2022-2-1
Here is my take on this subject: Replace xlsx excel file with a new file not working
When I use Matlab's "uiputfile" to select a pre-existing file for writematrix (and probably writetable too), the uI box pops up to ask "Replace file". I click "yes" to this followed by my "writematrix" command.
But the file is not replaced, only the selected range is replaced. To me this is a bug - not a "user should read the docs" issue - because my issue is with the relationship between uiputfile and writematrix. And to me, the docs are a bug alsoo in my mind.
Example follows on my work around to this issue:
filter = {'*.xlsx'};
[filename,filepath] = uiputfile(filter);
if isfile(filename)
% Disable warning
warnState = warning('off','MATLAB:DELETE:FileNotFound');
% Quirk: data always appends unless old file deleted first
delete [filepath filename]
% Restore warning
warning(warnState);
end
% Code here used to determine warnId for disable/re-enable warning
% [msgStr,warnId] = lastwarn;
%disp(warnId)
writematrix(time',[filepath filename],'Sheet',1,'Range','D2')
Walter Roberson
2022-2-1
uiputfile()'s question about replacing is a safety mechanism at the file selection level to ensure that the user does not accidentally use an existing file when they do nto want to do so. When the user selects "yes" to indicate that they do want to replace the file, there is no global variable set to remember that the file should be replaced, and there is no hidden information attached to the character vector filename or pathname to record the user's choice of overwrite or not. uiputfile()'s query about replacing is not remembered: if the user responds that they do not want to replace the file, then uiputfile() would prompt for a new file, rather than somehow record the information that the user is fine with the file being ammended in place.
In order for it to be otherwise, then uiputfile would have to be changed to have several possibilities:
- the user chose a file name that does not already exist
- the user chose a file name, and upon it being pointed out that the file already exists, the user wants to signify, "No, give me another chance to enter a name"
- the user chose a file name, and upon it being pointed out that the file already exists, the user wants to signify, "replace that file completely"
- the user chose a file name, and upon it being pointed out that the file already exists, the user wants to signify "allow appending to that file, but do not allow writing into the middle of the existing data"
- the user chose a file name, and upon it being pointed out that the file already exists, the user wants to signify "allow writing into the middle of that file"
Then somehow that chose of clear-file / append-only / overwrite-internally would have to be associated with the character vector object in a way that got propagated when the code uses fullfile() or did character vector concatenation or did string append to form the fully-qualified file name, and then the functions would have to recognize that augmented name and use it to make appropriate choices of overwriting.
Remember, you considered it a bug that writetable() did not know that the user had selected "Replace", rather than a deficiency in the information returned by uiputfile(), so in order to solve that bug it would not be enough for there to be an optional third output from uiputfile() that indicated which writing mode the user had chosen. Bug implies that it is a problem in uiputfile() and writetable() to fail to recognize the user option, and since uiputfile() only outputs character vectors at present, to fix that as a bug would require that uiputfile() would output something that acts just like a character vector for most purposes, but could somehow be queried as to which output mode the user chose.
Walter Roberson
2022-2-1
You do not indicate which version of MATLAB you are using. For the last several releases, writetable() has supported a 'WriteMode' option. It can be 'overwrite' or 'append' for text files, and 'replacefile', 'append', 'inplace' (the default), or 'overwritesheet'
Ron Fredericks
2022-2-1
编辑：Ron Fredericks
2022-2-1
Hi Walter:
Matlab version is : MATLAB: 9.11.0.1837725 (R2021b) Update 2, December 14, 2021
Thank you for looking at my "bug claim" issue. Very fast response.
But I completely disagree with your notion of replace vs writable. If uiputfile offers me a selection and warning that I am going to replace a file - then the contents of the old file being replaced should be gone when I then actually replace the file.
Then again, I see a "small" error in my code around the delete command as I have no parentheses. Thus all my "warning" controlls are useless and not needed.
Here is my preferred code...
labname = "test";
filter = {'*.xlsx'};
[filename,filepath] = uiputfile(filter);
if isfile(filename)
% Quirk: data always appends unless old file deleted first
delete ([filepath filename]);
end
lineNum = 1;
infoCol = 'B';
try
writematrix(labName,[filepath filename],'Sheet',1,'Range',[infoCol num2str(lineNum)])
catch
error("Excel sheet may be open, close sheet and try again...")
end
And in the process I identified and "fixed sort of" another problem - writing to a spreadhseet that was left open by the user (me).
I envite you to try this experiment:
1) run my code above creating a new spreadsheet
2) open the spreadsheet and add a "?" or other symbol into a blank cell, then close/save file
3) run my code again, selecting to replace the file with appropriate uiputfile useage.
4) open the spreadsheet again
If the file was actually replaced, the "?" or other symbol should not be there!
Without my "delete" command, the "?" will still be there and as such the user will not get a replaced file as "promised" by the uiputfile command. To me this is more than a doc issue (which I don't even see in my docs), but a bug in the relationship between uiputfile and writematrix (or writetable).
Walter Roberson
2022-2-1
- Successful execution of uiputfile returns the name of a new or existing file that the user specifies. It does not create a file.
[...]
- If the user clicks No in the warning dialog box, then control returns to the uiputfile dialog box, enabling the user to specify a different file name.
The name is all it returns. It does not create the file, it does not delete the file, and clicking No does not mean that the file is permitted to be overwritten in place.
You should look at uiputfile() as only being an interface to get a file name (and directory) from the user. It does not check permissions, does not create or delete files, just gets a name and directory.
Deleting a file if the user asks to replace would not be appropriate. When you delete a file, you lose the stored creation time and stored permissions associated with it. In all cases when you fopen() an existing file, no matter whether you ask to truncate the file or append to it or allow writing in the middle of it, the permissions are kept along with the creation time. Deleting the file automatically would go against standards of how file operations need to happen. See the details in the standards at https://pubs.opengroup.org/onlinepubs/9699919799/functions/fopen.html starting from If mode is w, wb, a, ab, w+, wb+, w+b, a+, ab+, or a+b
Les Beckham
2022-2-1
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
2022-2-2
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')
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
