8

I am building an application in C# in which I have to open a CSV file to read data from it. I get an exception when I try to open the CSV file from C# when that file is already open in Excel. The exception says that the process cannot access the file since it is already open. How can I solve this problem and open the file even if it is opened in other application?

Thanks, Rakesh.

Rakesh K
  • 8,237
  • 18
  • 51
  • 64

5 Answers5

9

I faced this problem some time back.

You are missing the FileShare parameter. Without specifying that, if you open a file, it will be locked exclusively by your application. But since it's already been opened by Excel (or any other app), you will receive an exception.

You can try using this - I think this will be your best bet -

using (FileStream fs = File.Open(<file-path>, FileMode.Open, FileAccess.Read, FileShare.Read))

This code says: Hello Excel! If you may permit (read, not throw exception), I would like to read the file, though I will not try to own it and I know that you may modify it anytime.

If this throws error, then Excel has denied you even the read access. Too bad then! All the best.

Nayan
  • 3,092
  • 25
  • 34
  • 2
    I found FileShare.Read did not work, while FileShare.ReadWrite did – David Sykes Dec 15 '11 at 10:48
  • Use FileShare.ReadWrite instead of FileShare.Read, as mentioned by Hans. – David Sykes Dec 15 '11 at 12:16
  • i had this problem and the `FileShare.ReadWrite` Fixed my problem after a while,only to notice that i had another using block where i called the method i got the exception on... so check your code, if you have this issue... something is definetly using the file... – Niklas Apr 18 '17 at 19:07
  • Thanks! FileShare.ReadWrite fixed my problem! – Jemil Oyebisi Jul 25 '18 at 17:05
8

It is possible but you have to carefully control the file sharing you specify. Most .NET classes default to FileShare.Read, denying another process from writing to the file. But that cannot work if the file is opened by Excel, it already gained write access to it. You cannot deny a right that was already acquired.

To fix the problem, make your code look similar to this:

        using (var fs = new FileStream(@"c:\\temp\\test.csv", FileMode.Open, 
                   FileAccess.Read, FileShare.ReadWrite))
        using (var sr = new StreamReader(fs)) {
            // Read it...
        }

Note the use of FileShare.ReadWrite. I verified this code works while Excel had test.csv opened.

Beware of the potential trouble you'll invite with this, odd things can happen when Excel writes to the file just as you are reading it. You'll likely read garbage, part of old data, part of new, without a good way to diagnose this.

Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536
0

Due to concurrency issues you can not have the option to write to two instances of the same file. It should be possible to open one as read-only this would allow for there to not be a concurrency issue as reading is guaranteed to be thread safe. This article should explain how to do what I proposed

Woot4Moo
  • 23,987
  • 16
  • 94
  • 151
  • I am opening the file from C# in Read-Only mode. But still I am getting this exception. What could be the problem and how can I solve it? – Rakesh K Oct 02 '10 at 16:54
0

That's not possible.

A file can be opened with different kind of protection. Excel opens the file exclusively, for the purpose of protecting the file from being changed by some other program and then reverted back when Excel saves it.

Excel could have opened the file and allowed reading, but then you could end up in a deadlock situation where two applications have the file open for reading, and neither can save anything back to it.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • That doesn't seem to be the case based on the link I provided. – Woot4Moo Oct 02 '10 at 17:00
  • @Woot4Moo: That article is about opening a spreadsheet, not a CSV file, and about opening a file for read-only access, not opening a file that is already opened exclusively, so I'm sorry but it's not relevant at all. – Guffa Oct 02 '10 at 17:06
  • actually it is quite relevant. If OP takes a look at the order in which the files are opened you can have Excel open the file first as a Read-Only instance which would potentially mitigate the issue – Woot4Moo Oct 02 '10 at 17:20
  • @Woot4Moo: Yes, that would be possible, but the article is about the API, not the Excel application. – Guffa Oct 02 '10 at 17:30
  • @Woot4Moo: No, it doesn't. Have you linked to the right article? – Guffa Oct 02 '10 at 18:31
  • From the article: Create an instance of the SpreadsheetDocument class from the document (This is the header) In the Open XML SDK, the SpreadsheetDocument class represents an Excel document package. – Woot4Moo Oct 02 '10 at 18:55
  • here is the code: using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false)) – Woot4Moo Oct 02 '10 at 18:55
  • @Woot4Moo: You are missing the point completely. I am talking about the *Excel Application*. If the OP was really opening the same file twice in different ways from his own program, don't you think that he would have asked about that instead? – Guffa Oct 02 '10 at 19:08
0

Another solution, suggested by this answer, is to copy the file to a temporary file and open that.

Use

System.IO.File.Copy(sourcepath, copypath, false);
Community
  • 1
  • 1
David Sykes
  • 48,469
  • 17
  • 71
  • 80