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

Last Reply one year ago By dharmendr

Posted one year ago

hi

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);
        fup5.SaveAs(excelPath);

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

        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            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))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();
            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";
                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
        }

    }

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

neda

 

Posted one year ago Modified on one year ago

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.

Code

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();
    ds.Tables.Add(dt);
    ExportDataSetToExcelAndDownload(ds);
}

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
        worksheet.Cells.Clear();
        // 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
    excelWorkBook.Save();
    // Close Excel
    excelWorkBook.Close();
    // Close Excel Application
    excelApp.Quit();
    // Download saved Excel
    Response.ContentType = ContentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
    Response.WriteFile(Server.MapPath("~/Test.xls"));
    Response.End();
}

Namespace

using Excel = Microsoft.Office.Interop.Excel;