System.Data.OleDb.OleDbException: External table is not in the expected format in ASP.Net

on May 17, 2017 06:14 AM


I use below code to import excel file into database

  protected void ImgloadB_Click(object sender, ImageClickEventArgs e)
        //Upload and save the file
        string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(fup5.PostedFile.FileName);

        string conString = string.Empty;
        string extension = Path.GetExtension(fup5.PostedFile.FileName);
        switch (extension)
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;

        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
            dtExcelData.Columns.AddRange(new DataColumn[77] {
             new DataColumn("Id", typeof(int)), new DataColumn("code", typeof(string)),
             new DataColumn("Type",typeof(string)), new DataColumn("Transfer", typeof(string)),
             new DataColumn("rahn", typeof(decimal)), new DataColumn("ejare", typeof(decimal)),
             new DataColumn("TotalPrice",typeof(decimal)), new DataColumn("MeterPrice", typeof(string)),
             new DataColumn("vam", typeof(string)), new DataColumn("pardakhti", typeof(string)),
             new DataColumn("zirbana",typeof(string)), new DataColumn("masahat", typeof(string)),

             new DataColumn("age",typeof(string)), new DataColumn("TimeTahvil", typeof(string)),
             new DataColumn("MalekName", typeof(string)), new DataColumn("Malekphone", typeof(string)),
             new DataColumn("MalkeMobile",typeof(string)), new DataColumn("MosName", typeof(string)),

             new DataColumn("MosPhone",typeof(string)), new DataColumn("MosMobile", typeof(string)),
             new DataColumn("State", typeof(string)), new DataColumn("City", typeof(string)),
             new DataColumn("Zone",typeof(string)), new DataColumn("district", typeof(string)),

             new DataColumn("position",typeof(string)), new DataColumn("AddressCa", typeof(string)),
             new DataColumn("AddressNa", typeof(string)), new DataColumn("TotalFloor", typeof(string)),
             new DataColumn("UnitFloor",typeof(string)), new DataColumn("Totalvahed", typeof(string)),

             new DataColumn("Unitvahed",typeof(string)), new DataColumn("bedroom", typeof(string)),
             new DataColumn("elevator", typeof(string)), new DataColumn("parking", typeof(string)),
             new DataColumn("Anbari",typeof(string)), new DataColumn("komod", typeof(string)),

             new DataColumn("tars",typeof(string)), new DataColumn("pasio", typeof(string)),
             new DataColumn("barberkio", typeof(string)), new DataColumn("kabinet", typeof(string)),
             new DataColumn("jenskaf",typeof(string)), new DataColumn("nama", typeof(string)),

             new DataColumn("gaz",typeof(string)), new DataColumn("shofaj", typeof(string)),
             new DataColumn("pakage", typeof(string)), new DataColumn("coolerG", typeof(string)),
             new DataColumn("coolerA",typeof(string)), new DataColumn("chiler", typeof(string)),

             new DataColumn("estakhr",typeof(string)), new DataColumn("sona", typeof(string)),
             new DataColumn("jakozi", typeof(string)), new DataColumn("irani", typeof(string)),
             new DataColumn("farangi",typeof(string)), new DataColumn("DescriptionPu", typeof(string)),

             new DataColumn("DescriptionKh",typeof(string)), new DataColumn("Image1", typeof(string)),
             new DataColumn("Image2", typeof(string)), new DataColumn("Image3", typeof(string)),
             new DataColumn("Image4",typeof(string)), new DataColumn("Image5", typeof(string)),

             new DataColumn("Video",typeof(string)), new DataColumn("change", typeof(string)),
             new DataColumn("metraj", typeof(string)), new DataColumn("date", typeof(DateTime)),
             new DataColumn("aparat",typeof(string)), new DataColumn("areaM", typeof(string)),

             new DataColumn("posSh",typeof(string)), new DataColumn("posJ", typeof(string)),
             new DataColumn("posSha", typeof(string)), new DataColumn("posGh", typeof(string)),

             new DataColumn("MalekNameN",typeof(string)), new DataColumn("MalekphoneN", typeof(string)),
             new DataColumn("MalekMobileN", typeof(string)), new DataColumn("Rahgiri", typeof(string)),
             new DataColumn("descriptionC",typeof(string)), new DataColumn("Contractdate", typeof(string)),
             new DataColumn("Expiredate",typeof(DateTime))


            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            string consString = ConfigurationManager.ConnectionStrings["DigimasterConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.estate_Archive";


but it make below error:


Server Error in '/' Application.

External table is not in the expected format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: External table is not in the expected format. Source Error:

Line 346:        using (OleDbConnection excel_con = new OleDbConnection(conString))
Line 347:        {
Line 348:            excel_con.Open();
Line 349:            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
Line 350:            DataTable dtExcelData = new DataTable();

Source File: D:\ordered site\AmlakeAbdi\AmlakeAbdi\amlak\فایل-پشتیبان.aspx.cs    Line: 348


best regards



Download FREE API for Word, Excel and PDF in ASP.Net: Download
on May 19, 2017 02:00 AM
on May 19, 2017 02:02 AM

Hi nedash,

Use the below code to export DataTable to Excel. In this code you will not get the error while importing to database. But you have to keep one blank excel with atleast one sheet in the project folder in order to write the datatable to the excel and download that saved excel. Here i have Test.xls in the project folder. For this i have used using Microsoft.Office.Interop.Excel library.


protected void Page_Load(object sender, EventArgs e)
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), new DataColumn("Name", typeof(string)), new DataColumn("Country", typeof(string)) });
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    DataSet ds = new DataSet();

private void ExportDataSetToExcelAndDownload(DataSet ds)
    // Initialize Excel Application
    Excel.Application excelApp = new Excel.Application();
    // Open Existing Excel
    Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(Server.MapPath("~/Test.xls"));

    foreach (DataTable table in ds.Tables)
        // Get first worksheet
        Excel.Worksheet worksheet = excelWorkBook.Worksheets[1] as Excel.Worksheet;
        // Clear all data from first sheet
        // Adding columns to sheet
        for (int i = 1; i < table.Columns.Count + 1; i++)
            worksheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
        // Adding rows to sheet
        for (int j = 0; j < table.Rows.Count; j++)
            for (int k = 0; k < table.Columns.Count; k++)
                worksheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
    // Save Excel
    // Close Excel
    // Close Excel Application
    // Download saved Excel
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");


using Excel = Microsoft.Office.Interop.Excel;