3

I'm trying overwrite a csv file that already exists in Matlab on Windows. The problem is sometimes I have the file open in Excel. When this is the case, the write operation fails.

Is there anyway to overwrite the file in Matlab? I do NOT want to use ActiveX to connect to the Excel session and edit the file that way.

Seeing this post about FileShare settings made me think it might be possible, but none of the fopen parameters seem to be able to do the trick. Excel may be locking the file with exclusive write access, in which case there is no getting around it. Does anyone know how to check this?

Example of problem:

% make csv file
x = magic(4);
csvwrite('foo.csv', x);

% open foo.csv in EXCEL

% try writing again
csvwrite('foo.csv', x);  % cannot write a new file
[fid, msg] = fopen('foo.csv' ,'w');  % cannot open handle for writing

As a side note, I used to be able to overwrite a file open in Excel when the file existed on a Linux box, and I had the file open over the network on a Windows box.

Community
  • 1
  • 1
Rich C
  • 3,164
  • 6
  • 26
  • 37

1 Answers1

2

Those FileShare settings are for files opened from the Win32 API's CreateFile family of functions, not the C style fopen family, which Matlab exposes. The fopen options won't get you there. See http://support.microsoft.com/kb/99173 for a quick rundown of the differences. (If you really wanted to use CreateFile or other Win32 I/O, e.g. to check to see if Excel has the file locked or lock it yourself, you could call it from Matlab through .NET using System.IO.File.)

Regardless, by default, Excel opens the file in write mode, and gets an exclusive (write) lock. So you couldn't open it for writing anyway, those file sharing settings would only let you open the file for reading. If you want to be able to overwrite an Excel file while it's open in Excel, you need to have Excel open it in read-only mode like katrasnikj suggests. This causes Excel to read it in to memory once and then release the filehandle.

Try turning on the Read-Only file attribute on these files after you write them by shelling out to the attrib command. This will cause Excel to open them read-only by default. Then clear the read-only attribute right before opening it from Matlab for rewriting.

if exist(file, 'file')
    [status,result] = system(sprintf('attrib -R "%s"', file));
end
[fid,msg] = fopen(file, 'w');
% ... write the file and close it ...
[status,result] = system(sprintf('attrib +R "%s"', file));

There's still a race condition between reader and writer, but if you write the file fast, it'll be a short window. Better would be to write out the csv to a temp file in the same directory, and then just turn off the read-only attribute long enough to swap the new file in to place with a movefile or java.io.File.renameTo. Still a race, but probably good enough to use in practice.

You could also change the permissions on the directory the files are in so your writer process has Modify permissions but the users running Excel only have Read access. Then the Excels will always open read-only and you don't have to fiddle with file attribs.

Andrew Janke
  • 23,508
  • 5
  • 56
  • 85