Click here to Skip to main content
15,949,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
While Expoting Excel Sheet And Importing That Sheet Got Error How To Fix This If I Change Exports Sheet Name Sheet Is Importing Otherwise It Got Error

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. 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() at iqmsampleworks_project.Areas.admin.Controllers.adminController.import(HttpPostedFileBase file, HttpPostedFileBase file1) in f:\Final\IQM SAMPLE WORK - Copy\Areas\admin\Controllers\adminController.cs:line 84


C#
public ActionResult import(HttpPostedFileBase file, HttpPostedFileBase file1)
{
    try
    {
   


        if (file1 != null)
        {
            file1.SaveAs(HttpContext.Server.MapPath("~/File/") + file1.FileName);

        }
        
       
        if (file != null)
        {
            string path = string.Concat(Server.MapPath("~/File3/" + file.FileName));
            if (System.IO.File.Exists(path))
            {
                System.IO.File.Delete(path);
             
            }
            
            file.SaveAs(path);
            //ViewData["Test"] = path;
            // Connection String to Excel Workbook
            string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = excelConnectionString;
            OleDbCommand command = new OleDbCommand("select * from [file.FileName]", connection);
            connection.Open();
            DataTable dt = new DataTable();

            dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int t = 0;
           
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[t] = row["TABLE_NAME"].ToString();
                t++;
            }
            
            DataSet ds = new DataSet();
            string query = string.Format("Select * from [{0}]", excelSheets[0]);
            using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connection))
            {
                dataAdapter.Fill(ds);
            }
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {

                ConfigurationTable model = new ConfigurationTable();
                string b = ds.Tables[0].Rows[i]["ID"].ToString();
                int c = Convert.ToInt16(b);
                int cnt = db.ConfigurationTables.Where(a => a.ID == c).Count();
                if (cnt == 1)
                {

                    ConfigurationTable model1 = db.ConfigurationTables.SingleOrDefault(e => e.ID == c);
                    model1.ID = Convert.ToInt16(ds.Tables[0].Rows[i]["ID"].ToString());
                    model1.DomainName = ds.Tables[0].Rows[i]["DomainName"].ToString();
                    model1.Type = ds.Tables[0].Rows[i]["Type"].ToString();
                    model1.Category = ds.Tables[0].Rows[i]["Category"].ToString();
                    model1.SampleName = ds.Tables[0].Rows[i]["SampleName"].ToString();
                    model1.DevelopedBy = ds.Tables[0].Rows[i]["DevelopedBy"].ToString();
                    model1.url = ds.Tables[0].Rows[i]["url"].ToString();
                    model1.UserType = ds.Tables[0].Rows[i]["UserType"].ToString();
                    model1.UserType1 = ds.Tables[0].Rows[i]["UserType1"].ToString();
                    model1.UserType2 = ds.Tables[0].Rows[i]["UserType2"].ToString();
                    model1.UserType3 = ds.Tables[0].Rows[i]["UserType3"].ToString();
                    model1.UserType4 = ds.Tables[0].Rows[i]["UserType4"].ToString();
                    model1.UserType5 = ds.Tables[0].Rows[i]["UserType5"].ToString();
                    model1.UserType6 = ds.Tables[0].Rows[i]["UserType6"].ToString();
                    model1.U
Posted
Updated 22-Jan-14 10:59am
v4
Comments
Richard MacCutchan 22-Jan-14 5:44am    
Show the code where the error occurs.
sailaja12345672 22-Jan-14 6:10am    
Code moved to OP's question.
sailaja12345672 22-Jan-14 7:09am    
connection.Open();

1 solution

The error means that the sheet is not in the format that your code expects ( for example, a cell has the wrong data type ). That's why it works for one sheet and not another.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900