System.Data.OleDb.OleDbException: Unspecified error

I was trying to access the excel sheet using  Oledb, but as soon as try to open the connection to that excel file i got error like "UnSpecified error"... tried with different drivers and nothing worked. Tried giving full access to 'Everyone' for the particular folder but nothing solved the issue.



the sample code is....

  if (_fullFilePath.EndsWith(".csv", StringComparison.CurrentCultureIgnoreCase))

                {

                    var dirPath = Path.GetDirectoryName(FullFilePath);

                    var fileName = Path.GetFileName(FullFilePath);

                    CreateCsvSchemaIniFile(dirPath, fileName);

                    //_dbConnection = new OleDbConnection(

                    //    String.Format(

                    //        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='text;HDR=Yes;FMT=Delimited'",

                    //        dirPath));

                     string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt; Persist Security Info=False";

                   //  string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt;Persist Security Info=False";

                     System.Data.Odbc.OdbcConnection _dbConnection;

                     _dbConnection = new System.Data.Odbc.OdbcConnection(

                                    strConnString.Trim());

                    _dbConnection.Open();

                    strReaderQuery = CSV_QUERY + "[" + fileName + "]"; // kaf causes error when file name has spaces

After lot of research i found that, it was a problem with impersonation issue. I have set impersonation to true to fix some other problem and that was the issue here. When we read data from  excel file , these data will be stored in temp folder and these folders will be accessible only by ASPNET  account. So, if you use impersonation it will be overridden and that cause the issue. Just changed the impersonation to false and that solved the issue. Now the temp folder is having the proper authorization and i got rid of the issue. This is not only specific to reading excel file, whenever you read something in systems memory, make sure that you don't override the impersonation.