Reading an Excel file using OleDB C # when it is being used by another process

Question

I try to read an excel file every 2 seconds, this file is updated by another RTD application.

I can read this file over the Oledb connection, but the problem occurs when I try to read it every 2 seconds. Of 10 attempts, he can read only 4-5 times, and with other attempts, he throws an exception.

Connection string

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\nids\shes.xlsm;Extended Properties="Excel 12.0 Macro;HDR=Yes;IMEX=1" 

the code

 //opening connection to excel file using (OleDbConnection connection = new OleDbConnection(constr))//constr = connection string { try { connection.Open(); isconopen = true; } catch { dispatcherTimer2.Start(); connection.Close(); isconopen = false; } // If connection is ok , then query sheet if (isconopen == true) { strcon = "SELECT * FROM [" + dsheet + "]"; using (OleDbDataAdapter adapter = new OleDbDataAdapter(strcon, connection)) { try { adapter.Fill(result); isread = true; adapter.Dispose(); connection.Close(); } catch { isread = false; dispatcherTimer2.Start(); adapter.Dispose(); connection.Close(); } } } //if able to retrieve data then call some other function if (isread == true) { converToCSV(0);// for further processing } 

Please help me, I have been trying this since the last 1 month. Please, please, please help me.

Answer 1

Sadly OleDB driver will open the file exclusively by default, then you cannot open it when it is used by someone else, even for reading.

Two considerations:

• Another application may exit the file in milliseconds, so it’s useful to try again.
• The driver will always open the file for writing (therefore, you cannot open it twice through OleDB), but it is divided for reading (therefore, you can copy it).

I said that first try opening it after a short pause, if it is still in use (and you cannot wait any longer), then you can make a copy and open it.

Suppose you have code in the HandlExcelFile() function:

 void HandleExcelFile(string path) { try { // Function will perform actual work HandleExcelFileCore(path); } catch (Exception) // Be more specific { Thread.Sleep(100); // Arbitrary try { HandleExcelFileCore(path); } catch (Exception) { string tempPath = Path.GetTempFileName(); File.Copy(path, tempPath); try { HandleExcelFileCore(tempPath); } finally { File.Delete(tempPath); } } } } 

The code is a little ugly, so just think that this is the starting point for writing your own function.

Questions:

• Repetition is not so bad, and this is the usual way to solve such problems. This, for example, is what the Windows shell does (and it's even more normal with networks).
• If the application did not close the file, you can copy (and read) the old data. If you always need the most up-to-date data, you have only one choice: wait. If you can assume that the unsaved data refers to the previous time frame (T-1, as in digital electronic, when the edge of the signal is on the edge of the clock), just do it and live happily.

Unsolicited solution:

I have not tried this, so you need to do it yourself. In fact (initially I was wrong) you can open the connection read-only (through advanced properties). It is not documented if it applies only to the connection, or to both the file and the connection. In any case, try changing the connection string to:

 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\nids\shes.xlsm;Extended Properties="Excel 12.0 Macro;HDR=Yes;IMEX=1;ReadOnly=true" 

Just added ReadOnly=true at the end of Extended Properties .

Other solutions:

• The only alternative solution that comes to my mind is ... to manually read the Excel file (so you can open it for reading only). However, even in this case, another application may not have written new data (so you read the old one).
• Do not use the timer at all. Change your design to FileSystemWatcher , you will read the file only after the notification is changed.
• When applicable ... just don't use the shared file as an IPC mechanism! You may not be able to change the second application, so this may not be your business.
• Do not use OleDB to read Microsoft Excel files, there are many third-party free libraries that do not open a file with an exclusive lock, for example this one .
• Do not read data directly from files, if an instance of Microsoft Excel always works, you can use COM interaction to receive notifications. Check out this general article on COM add-ins, and this see how you can attach your C # application using Office Interop to an existing instance of Microsoft Excel.

Answer 2

Not sure why you want to read excellence the way you do it.

You can try LinqToExcel for reading Excel, its small little library for reading excel files as well, if you need to create excel then try to EPPLUS library. This library, which I personally found very effective when working with Excels

Answer 3

I had a similar problem. Below fixed work for me

1) Do not hold the connection to the sheet. Instead, open the data to read the connection and immediately close the connection.

2) If you use managed code in an unmanaged application, consider using a managed type object instead of a pointer (using gcnew) and use Stack Semantics to clear memory when the object goes out of scope.