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
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:
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:
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:
FileSystemWatcher
, you'll read the file only when notified it has been changed.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With