SqlBulkCopy Excel ASP.Net: External table is not in the expected format

Last Reply on Dec 10, 2013 06:02 AM By Pradip

Posted on Dec 10, 2013 02:42 AM
string ExcelContentType = "application/vnd.ms-excel";
string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

if (fileuploadExcel.HasFile)
{
    //Check the Content Type of the file
    if (fileuploadExcel.PostedFile.ContentType == ExcelContentType || fileuploadExcel.PostedFile.ContentType == Excel2010ContentType)
    {
            try
            {
                //Save file path
                string path = string.Concat(Server.MapPath("~/TempFiles/"), fileuploadExcel.FileName);
                //Save File as Temp then you can delete it if you want
                fileuploadExcel.SaveAs(path);

                string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

                // Create Connection to Excel Workbook
                using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                {
                    OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        // SQL Server Connection String
                        string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
                        // Bulk Copy to SQL Server
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                        {
                            bulkCopy.DestinationTableName = "Excel_table";
                            bulkCopy.WriteToServer(dr);
                            lblMessage.Text = "The data has been exported succefuly from Excel to SQL";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
        }
    }

If I upload .xlsx File It's Working Fine But .xls Throws The error External table is not in the expected format

Posted on Dec 10, 2013 04:21 AM

Seems like you are opening Excel 2007 or higher file but use OLEDB Jet. Make use of OLEDB ACE driver

http://www.microsoft.com/en-in/download/details.aspx?id=13255


Posted on Dec 10, 2013 06:02 AM

Solve This Any File Upload Using This Connectionstring 

 

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html