Read Excel file with OleDB c#, when it is used by

Question

I am trying to read an excel file every 2 seconds, This file is getting updated by other RTD application.

I am able to read this file by Oledb connection, but problem comes when i am trying to read it every 2 seconds. Out of 10 attempts it is able to read 4-5 times only and at other attempts ,it throws 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"

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 am trying this from last 1 month. Please please please please help me out

Answer 1

Sadly OleDB driver by default will open file exclusively then you can't open it when it's in use by someone else, even just for reading.

Two considerations:

• Other application may finalize its work with the file within milliseconds so it's good to try again
• Driver will always open file locked for writing (so you can't open it twice via OleDB) but it's shared for reading (so you can copy it).

That said I suggest you should first try to open it after a short pause, if it's still in use (and you can't wait more) then you can make a copy and open that.

Let me assume you have your code in a 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);
            }
        }
    }
}

Code is little bit ugly so just consider it a starting point to write your own function.

Considerations:

• Retry isn't such bad thing and it's a common way to solve this kind of problems. It's, for example, what Windows shell does (and it's even more normal with networks).
• If application didn't close the file then you may copy (and read) old data. If you always need most up-to-date data then you have only one choice: wait. If you can assume that unsaved data belongs to the previous time frame (T - 1, as in digital electronic when signal edge is on clock edge) then just do it and live happy.

Untested solution:

I didn't try this so you have to do it by yourself. Actuallly (I was initially wrong) you can open a read-only connection (through extended properties). It's not documented if this apply to connection only or both file handle and connection. Anyway let's try to change your 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 a ReadOnly=true at the end of Extended Properties.

Other solutions:

• The only alternative solution that comes to my mind is to...manually read Excel file (so you can open it just for reading). That said even in this case the other application may have not written new data (so you'll read old one).
• Don't use a timer at all. Change your design to use a FileSystemWatcher, you'll read the file only when notified it has been changed.
• When applicable...just do not use a shared file as IPC mechanism! Well, you may not be able to change 2nd application so this may not be your case.
• Do not use OleDB to read Microsoft Excel files, there are many 3rd part free libraries that don't open file with exclusive lock, for example this one.
• Do not read data directly from files, if Microsoft Excel instance is always running you can use COM interop to get notifications. Check this general article about COM add-ins and this to see how you can attach your C# application with Office Interop to an existing Microsoft Excel instance.

Answer 2

Not sure , why do you want to read the excel the way you are doing.

You can try LinqToExcel for excel reading , its a nice little library for reading excel files also if you need to create excel then try to EPPLUS library. These library i personally found really effective when working with Excels

Answer 3

I had similar problem. Below fixes worked for me

1) Don't hold your connection to sheet. Instead open connection read data and close the connection immediately.

2) If you are using managed code in the unmanaged application then consider using object of managed type instead of pointer(using gcnew) and use Stack Semantics to make sure that memory is cleaned up when object goes out of scope.