Readmatrix does not work when the Excel file is open

I tried to read some data from an Excel sheet using readmatrix. It works perfectly when the Excel file is closed. But, I received an error when the Excel is open, indicating that the function can not open the Excel file. Does anyone know what problems would be and how I can solve this problem? I keep using Excel for input data, becasue of the better overview.

[Ameer Hamza]

Which MATLAB release are you using and on what OS? I tried it with R2020a on macOS, and there is no such issue. Alternatively, you may consider using xlsread().

[Walter Roberson]

Excel locks file a completely different way for Windows compared to MacOS. This is a problem for Windows but not for Mac.

If you are using R2019b or later, 'UseExcel' should default to false; I wonder if you have it set to true? Though really, the Windows way of locking can apply even if you are not using Excel for the interface. On the other hand, if Excel is already open then talking to it should get you the current values of the variables...

[Walter Roberson]

R2019b and R2020a default to not using Excel for readmatrix() and readtable() even on Windows systems with Excel.

[John Hoop]

Hi Walter, thank you very much. I am using R2020a and also put False on UseExcel. Somehow, it does not work as long as the active Excel file remains open. The only work around is to first close the Excel file each time before reading data. Very frustrating for such a seemly simple thing.

[John Hoop]

Hi Ameer, Thanks. I am using Windows 10 and R2020a.

[Walter Roberson]

try putting in true for useexcel to experiment

Answer 1

[Sai Sri Pathuri]

This is an expected behaviour in windows. Files get "locked" when opened and can often not be accessed by other applications.

[Kirk Dolan]

MATLAB 2022a still has the same problem of not reading Excel file if it's open. How do we fix this? It's very frustrating.

[Walter Roberson]

@Kirk Dolan

Use the readmatrix() option 'UseExcel', true

That will ask MATLAB to talk to Excel, and since Excel is what has the file open, in theory Excel should be able to return the data.

The behaviour itself is at the operating system level.

Any time you have two different processes that might want to access the same file at the same time, you have a potential problem if one of the processes might be writing to the file at the same time that the other process is reading from it. For example the reading process might read "there are 5 strawberries", and then the writing process might take away one of the strawberries in the file, and then the reading process asks to read the 5th strawberry which no longer exists there...

There are only a small number of possible operating system strategies for dealing with this situation:

1. The operating system can "lock" the file the very first time a process asks to use the file, and not let go of the lock until the process releases it, and deny all other processes access to the file while the lock exists.That way it is impossible for two processes to get conflicting versions of the file. On the other hand, it means that a process can deliberately or accidentally lock an important resource just by asking to read the file. This is a system called "hard-locks": the prevention against multiple access is enforced for everyone, and no normal process can bypass it.
2. The operating system can keep track of all the existing readers of a file, and allow them all to read from the file simultaneously as long as they only want to read from the file. The first time that a process asks to write to the file, the operating system puts in a lock, and starts denying all new access to the file, and waits for all currently reading processes to finish with the file, before finally granting the writing process access. As long as the writing process has write access, the operating system refuses all new access until the writing process gives up write access. This is a system called "hard-locks": the prevention against multiple access is enforced for everyone, and no normal process can bypass it.
3. The operating system might simply not offer any locking mechanism at all, leaving it up to each program to figure out how to get safe shared access to a file. This is risky and tends to lead to file corruption.
4. The operating system might default to shared access, but might also offer a way to create "advisory" (or "soft") locks, where a process can assert an interest in reading or writing a file, and can effectively volunteer to wait for the resource to become available according to every other process that is also volunteering to use the same soft-lock structure. File corruption can still occur if some other process does not use the voluntary system, or if some process makes a mistake in how it handles the voluntary requests.

Sometimes instead of outright refusing access, processes are queued for access in request order, possibly with a timeout. This is still "hard-locks" if the locking is mandatory instead of advice. When access is queued, you can run into a situation called "deadlock" (or "deadly embraces") where one process cannot release access to a resource until it has access to another second resource, but that another process currently controls the second resource and (for whatever reason) cannot give it up until it has access to the first resource. Neither one can proceed because they are both waiting for the other one to finish. This is a hard problem to solve. Systems that do not offer queuing for a resource have the problem that a process that wants access has to "spin" (continually testing quickly until it gains access, actively using the CPU as it goes) or else to go into a cycle of napping (giving up control of the CPU for a time) and testing again.

Sometimes there is a variant of the above hard-locks that allows processes with a specific priviledge to access the file even though it is locked, with such access typically being given to a backup function that is responsible for knowing enough about how the file is used to be able to make a coherent backup of the file even while it is in use.

MS Windows uses strategy #1: hard-locks that prevent any other process from accessing a file for reading or writing as long as another process has it open. MS Windows adds to the arrangement a way for a process to specifically tell MS Windows that shared reading is acceptable, or that shared writing is acceptable. A process that only intends to read from a file would routinely volunteer to give up exclusive access as soon as it opened the file.

MacOS uses only strategy #4, advisory-locks (and access controls to restrict who can access), as is also true for the majority if linux (a few linux have hard-locks.)

MATLAB does not have any ability to bypass hard-locks; they are at the operating system level, and if the process did not volunteer to give up exclusive access then no other process can do anything about it (except perhaps some processes with special privileges.)