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

[Stephen23]

"...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.

[Dominik]

Agreed, this was confusing. should be more comprehensive now.

Thanks Stephen

I added the files for better understanding:

test.xlsx:

ID data

1110001J03Rik 8.89677566558355

1110008J03Rik 7.96109736966

0610012G03Rik 9.63646621146629

1110008F13Rik 11.1951001197166

1110008P14Rik 9.66131244366283

1110020G09Rik 10.1350186373341

1190005I06Rik 8.31154145339037

1190007I07Rik 7.80357654149566

1300010F03Rik 11.8919227299957

1300018J18Rik 10.1833018264752

test1.xlsx

ID data

0610012G03Rik 9.63646621146629

1110001J03Rik 8.89677566558355

1110008F13Rik 11.1951001197166

1110008J03Rik 7.96109736966

1110008P14Rik 9.66131244366283

1110020G09Rik 10.1350186373341

1190005I06Rik 8.31154145339037

1190007I07Rik 7.80357654149566

1300010F03Rik 11.8919227299957

1300018J18Rik 10.1833018264752

test2.xlsx

ID data

1110001J03Rik 8.89677566558355

1110008J03Rik 7.96109736966

0610012G03Rik 9.63646621146629

1110008F13Rik 11.1951001197166

1110008P14Rik 9.66131244366283

Answer 1

[Stephen23]

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:

1. write ten values to a (new?) spreadsheet.
2. 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.
3. 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.

[Dominik]

Now I got it.

Thanks!

[Philip Borghesani]

(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]

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.

Answer 2

[Dominik]

OK I see. this is the doc file, however the help writetable is misleading to me.

This is different from what I get:

writetable overwrites any existing file

which writetable

/usr/local/MATLAB/R2018b/toolbox/matlab/iofun/writetable.m

help writetable:
writetable Write a table to a file.
    writetable(T) writes the table T to a comma-delimited text file. The file name is
    the workspace name of the table T, appended with '.txt'. If writetable cannot
    construct the file name from the table input, it writes to the file 'table.txt'.
    writetable overwrites any existing file.
 
    writetable(T,FILENAME) writes the table T to the file FILENAME as column-oriented
    data. writetable determines the file format from its extension. The extension
    must be one of those listed below.
 
    writetable(T,FILENAME,'FileType',FILETYPE) specifies the file type, where
    FILETYPE is one of 'text' or 'spreadsheet'.
 
    writetable writes data to different file types as follows:
 
    .txt, .dat, .csv:  Delimited text file (comma-delimited by default).
 
           writetable creates a column-oriented text file, i.e., each column of each
           variable in T is written out as a column in the file. T's variable names
           are written out as column headings in the first line of the file.
 
           Use the following optional parameter name/value pairs to control how data
           are written to a delimited text file:
 
           'Delimiter'      The delimiter used in the file. Can be any of ' ',
                            '\t', ',', ';', '|' or their corresponding names 'space',
                            'tab', 'comma', 'semi', or 'bar'. Default is ','.
 
           'WriteVariableNames'  A logical value that specifies whether or not
                            T's variable names are written out as column headings.
                            Default is true.
 
           'WriteRowNames'  A logical value that specifies whether or not T's
                            row names are written out as first column of the file.
                            Default is false. If the 'WriteVariableNames' and
                            'WriteRowNames' parameter values are both true, T's first
                            dimension name is written out as the column heading for
                            the first column of the file.
 
           'QuoteStrings'   A logical value that specifies whether to write
                            text out enclosed in double quotes ("..."). If
                            'QuoteStrings' is true, any double quote characters that
                            appear as part of a text variable are replaced by two
                            double quote characters.
 
           'DateLocale'     The locale that writetable uses to create month and 
                            day names when writing datetimes to the file. LOCALE must
                            be a character vector or scalar string in the form xx_YY.
                            See the documentation forDATETIME for more information.
 
           'Encoding'       The encoding to use when creating the file.
                            Default is 'system' which means use the system's default
                            file encoding.
 
    .xls, .xlsx, .xlsb, .xlsm, .xltx, .xltm:  Spreadsheet file.
 
           writetable creates a column-oriented spreadsheet file, i.e., each column
           of each variable in T is written out as a column in the file. T's variable
           names are written out as column headings in the first row of the file.
 
           Use the following optional parameter name/value pairs to control how data
           are written to a spreadsheet file:
 
           'WriteVariableNames'  A logical value that specifies whether or not
                            T's variable names are written out as column headings.
                            Default is true.
 
           'WriteRowNames'  A logical value that specifies whether or not T's row
                            names are written out as first column of the specified
                            region of the file. Default is false. If the
                            'WriteVariableNames' and 'WriteRowNames' parameter values
                            are both true, T's first dimension name is written out as
                            the column heading for the first column.
 
           'DateLocale'     The locale that writetable uses to create month and day
                            names when writing datetimes to the file. LOCALE must be
                            a character vector or scalar string in the form xx_YY.
                            Note: The 'DateLocale' parameter value is ignored
                            whenever dates can be written as Excel-formatted dates.
 
           'Sheet'          The sheet to write, specified the worksheet name, or a
                            positive integer indicating the worksheet index.
 
           'Range'          A character vector or scalar string that specifies a
                            rectangular portion of the worksheet to write, using the
                            Excel A1 reference style.
 
           'UseExcel'      A logical value that specifies whether or not to create the 
                           spreadsheet file using Microsoft(R) Excel(R) for Windows(R). Set 
                           'UseExcel' to one of these values: 
                                true  -  Opens an instance of Microsoft
                                         Excel to write (or read) the file.
                                         This is the default setting for
                                         Windows systems with Excel
                                         installed.
                                false -  Does not open an instance of
                                         Microsoft Excel to write (or read)
                                         the file. Using this setting may
                                         cause the data to be written
                                         differently for files with live
                                         updates (e.g. formula evaluation
                                         or plugins).
 
    In some cases, writetable creates a file that does not represent T exactly, as
    described below. If you use TABLE(FILENAME) to read that file back in and create
    a new table, the result may not have exactly the same format or contents as the
    original table.
 
    *  writetable writes out numeric variables using long g format, and
       categorical or character variables as unquoted text.
    *  For non-character variables that have more than one column, writetable
       writes out multiple delimiter-separated fields on each line, and constructs
       suitable column headings for the first line of the file.
    *  writetable writes out variables that have more than two dimensions as two
       dimensional variables, with trailing dimensions collapsed.
    *  For cell-valued variables, WRITE writes out the contents of each cell
       as a single row, in multiple delimiter-separated fields, when the contents are
       numeric, logical, character, or categorical, and writes out a single empty
       field otherwise.
 
    Save T as a mat file if you need to import it again as a table.

[Stephen23]

"...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]

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]

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]

@Ron Fredericks

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]

@Ron Fredericks

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]

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]

https://www.mathworks.com/help/matlab/ref/uiputfile.html

• 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]

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]

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')