none
Excel Processing using OLEDB RRS feed

  • Question

  • Occasionally I am getting errors, when reading excel files using OLEDBConnection. The excel file has got links to other data sources.

    Below is the error I am getting

    System.Runtime.InteropServices.SEHException (0x80004005): External component has thrown an exception.
       at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()


    Appreciate if anyone can provide solution into this.

    Monday, July 9, 2018 9:27 AM

All replies

  • This error is too generic I'm afraid. You're having issues opening an external document. It could be any # of reasons ranging from a network error to someone already having it open in read only mode. Could also be related to leaking resources, depending upon how long your app has been running.

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, July 9, 2018 2:47 PM
    Moderator
  • Would it be possible for you to upload somewhere your Excel file so that I may take a look at it and try to reproduce your issue?

    Also just in case, you may want to try reading and processing your Excel files in C# with some alternative way. Perhaps the issue is specific to OLEDB code, not the Excel content, in which case I would need to check your exact code as well.

    Tuesday, July 10, 2018 7:37 AM
  • The issue is I am not always getting this error. When multiple excel files are processed at the same time this error is thrown. But when individual files are processed, no such issues. The excel file has got links to multiple data sources, which are not accessible since I am running it from another domain.
    Tuesday, July 10, 2018 9:52 AM
  • It's a windows service application developed in .Net 4.0. This service will act as a watch dog and will process the excel files as and when the file gets uploaded to a shared folder.

    I have noticed that this issue occurs when multiple excel files are processed/read by windows service at once. Also this excel file has got links to other sources.

    I am afraid, I wont be able to share that excel file.

    Tuesday, July 10, 2018 9:53 AM
  • Can you post the relevant code that is failing? The top things that come to my mind:

    • Not properly cleaning up resources/connections
    • Trying to open the same file twice
    • Something to do with the fact that it is a service (i.e. fails before the network is fully initialized or something).

    It would be useful if you could capture some logs to determine when it fails.

    1. Does it fail on the first file but not subsequents?
    2. Or, does it fail on a later file but not the first?
    3. Does it fail for the same set of files or does it eventually process them?
    4. Are the files that it fails on generally large or small?
    5. Are the files it fails on non-standard (e.g. in Unicode while most are in ASCII or in another language while most are in English)?

    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, July 10, 2018 1:43 PM
    Moderator
  • It normally does not fail on the first file. I have noticed that it processes around 20+ files and then it fails. But if you restart the service, the errored file will get successfully processed. The files are generally around 20 MB in size.
    Wednesday, July 11, 2018 6:25 AM
  • Hello,

    To better understand how you are reading the files it would be beneficial to see your code. Here is an example of what typical read to DataTable might look like except for the connection string which in this case uses a class to dynamically build the connection string.

    public DataTable ReaDataTable(string pFileName)
    {
        var dt = new DataTable();
        var selectStatement = "SELECT * FROM [Customers$]";
        var con = new SmartConnection();
    
        try
        {
            using (var cn = new OleDbConnection {ConnectionString = con.ConnectionString(pFileName, 1, ExcelHeader.Yes)})
            {
                using (var cmd = new OleDbCommand {Connection = cn, CommandText = selectStatement})
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection) ?? throw new InvalidOperationException());
                }
            }
        }
        catch (OleDbException oe)
        {
            // isolate specific exception and record to log
        }
        catch (Exception e)
        {
            // record exception to log of some sort
        }
    
        return dt;
    }
    

    Connection string builder

    using System.Data.OleDb;
    
    namespace ExcelOperations.OleDbWork
    {
        public enum ExcelHeader
        {
            Yes,
            No
        }
    
        public class SmartConnection
        {
            public string ConnectionString(string pFileName, int pImex = 1, ExcelHeader pHeader = ExcelHeader.No)
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                if (System.IO.Path.GetExtension(pFileName)?.ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                    builder.Add("Extended Properties", $"Excel 8.0;IMEX={pImex};HDR={pHeader.ToString()};");
                }
                else
                {
                    builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                    builder.Add("Extended Properties", $"Excel 12.0;IMEX={pImex};HDR={pHeader.ToString()};");
                }
    
                builder.DataSource = pFileName;
    
                return builder.ConnectionString;
            }
    
            public string ConnectionStringExporter(string pFileName, int pImex = 1, ExcelHeader pHeader = ExcelHeader.No)
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
                builder.Provider = "Microsoft.ACE.OLEDB.12.0";
                builder.Add("Extended Properties", $"Excel 12.0 Xml;IMEX={pImex};HDR={pHeader.ToString()};");
    
                builder.DataSource = pFileName;
    
                return builder.ConnectionString;
            }
    
        }
    }
    

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 11, 2018 10:10 AM
    Moderator
  • It sounds like a resource issue to me. Can you post the relevant code? Specifically we're interested in whether you're properly cleaning up all the resources associated with the OLEDB connection and commands.

    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, July 11, 2018 2:09 PM
    Moderator
  • I am reading excel using ACE.OLEDB.12 engine. When multiple files are being read, the application throws the below error. This error is thrown only when multiple excel files are processed. I am closing the connection and using OleDbConnection.ReleaseObjectPool() to release the pool.

    When using memory profiler, there are instances which are garbage collected without being properly disposed. Those instances include System.Data.Oledb.DataSourceWrapper and System.Data.Oledb.SessionWrapper. Is this the reason for the below error? If so what should be done to dispose this? Please suggest.

    The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()

    Saturday, January 12, 2019 10:26 AM
  • All ADO.NET connections have to be disposed. You didn't post the code you use to read so we cannot tell you have to fix it.

    Michael Taylor http://www.michaeltaylorp3.net

    Saturday, January 12, 2019 5:24 PM
    Moderator
  • using (OleDbConnection oledbConn = new OleDbConnection(GetExcelConnectionString(pExcelType, pFilePath))) { //do operations oledbConn.Close(); OleDbConnection.ReleaseObjectPool(); return true; }

     private string GetExcelConnectionString(string strExcelType, string strFilePath)
            {
                string connString = "";
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1;MODE=READ;READONLY=TRUE\"";
                return connString;
            }

    • Edited by ANair Sunday, January 13, 2019 2:46 PM
    Sunday, January 13, 2019 7:45 AM
  • Hi ANair,

    The following article share a solution that grant the NETWORK SERVICE account modify permission on folder where excel files will sit.

    Please check if it works for you.

    https://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. 
    Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. 
    There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet. 

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 14, 2019 8:56 AM
    Moderator
  • NETWORKSERVICE permission is already provided Zhanglong.
    Monday, January 14, 2019 11:14 AM
  • Hi ANair,

    Copy the file to another folder and grant the "EveryOne" account modify permission and check if it works for you. 

    If the issue still exists, could you please share a simple sample that could reproduce the issue via OneDrive.

    Best regards,

    Zhanglong 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 16, 2019 6:28 AM
    Moderator