Opening file in read-only in Excel causes LV write permission error

[eberaud]

Hi,

 

I can't get my head around this issue. Let me lay out the scene:

 

My application logs data in a .csv file. The file is created with R/W permissions through the "Open/Replace/Create File" VI.  Then it uses the "Write to Binary File" to log the data, keeps the reference opened all along, and only close the file at the end.

 

So far everything works perfectly. If I double click on the file in Windows Explorer, Excel sees that the file is being opened in LabVIEW and shows the typical message giving me the following choice: Read-only, Notify, or Cancel. If I cancel, Excel closes and things keep on going fine.

 

However, if I do click "Read only", the "Write to Binary File" function in my LabVIEW application throws an error 8: File permission error. Somehow opening the file as read-only in Excel steals the write permission of LabVIEW.

 

Have you ever experienced this? What could be happening?

 

Emmanuel

[ned]

Can you share your code, or better, a minimal VI that demonstrates the problem? In a similar situation, I wrote code that closed the file after every write and immediately set the Read-Only flag on the file, so that Excel would always open it Read-Only. When I needed to append to the file, it cleared the Read-Only flag and immediately opened it with write access. Kind of inelegant, but it worked. The user could open the file in Excel at (almost) any time and see the most recent results, without interfering with the ability of my code to update the file.

[eberaud]

The thing is that my application is quite heavy (automation, data logging, hardware drivers, etc.) and I don't want to make it heavier by opening/closing the file every time I write into it (which is every 500ms).

[LogMAN]

I've tried to recreate your situation, but it seems to work for me. I can write while Excel (2010) has read-only access. I guess you should check your project again, if the file has been closed at some point. Here is the snippet of my Test-VI:

[eberaud]

Thank you. I have performed more tests and found that the problem would only happen with big files (at least 3MB).

 

What happens is that my application loses the write permission only while Excel is loading the file, then it gets it back. So a bigger file means a longer loading time... I changed my code to keep trying for a maximum of 20s, then it gives up. This actually works remarkably well since I already had a queuing system, which means the "file writer" can catch up after having wasted time in this retrying loop.

 

Does Windows somehow lock the file while it is being opened to guarantee the integrity of the data?

[LogMAN]

Good question. I just tested on my system again with a much highter writing rate of 1ms: Now I get the exact same error. So as I understand it, write access guaranties that no other application can write the file, but it does not guaranty file access at all times (for example if another application reads the file). There is some sort of locking procedure to ensure no data is changed while reading, however I never got much into Windows file handling.

 

I suggest you avoid this situation entirely by not accessing the file while writing to it. You could split files after a couple of MB or a certain time. Your current solution works too, but you can't tell how much data will have to be buffered before a write operation is possible again (so the system might run out of memory in the worse case). A database is the best choice for shared access (and it is quite easy to query data into Excel). Not entirely sure about TDMS (in terms of shared access), but for measurement data it is the most efficient.

[Rolf Kalbermatter]

Good question. I just tested on my system again with a much highter writing rate of 1ms: Now I get the exact same error. So as I understand it, write access guaranties that no other application can write the file, but it does not guaranty file access at all times (for example if another application reads the file). There is some sort of locking procedure to ensure no data is changed while reading, however I never got much into Windows file handling.

 

I suggest you avoid this situation entirely by not accessing the file while writing to it. You could split files after a couple of MB or a certain time. Your current solution works too, but you can't tell how much data will have to be buffered before a write operation is possible again (so the system might run out of memory in the worse case). A database is the best choice for shared access (and it is quite easy to query data into Excel). Not entirely sure about TDMS (in terms of shared access), but for measurement data it is the most efficient.

 

Most likely this is Excel itself that is doing this. While loading the data file, imagine what would happen if another application is at the same time writing to it. This could result in inconsistent data being read during the loading process so Excel simply disallows write access for other applications during loading (and probably fails the load if another application has already opened the file for write access). After the file is loaded and all data is safely in memory, Excel probably simply closes the file altogether or at least resets the DENY rights on the file. The read only settings when opening a file is not so much how Excel opens the file itself, but an internal setting of a workbook that disables editing of that workbook.