Click here to Skip to main content
15,909,896 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
If Upload Excel File To sql Server Database If I Use .xlsx File Then it's Working Fine If i use the.xls then Error External table is not in the expected format.

C#
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;\"";
          // string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

            // 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;
        }
    }
}



connection.Open();
throws The Error Error External table is not in the expected format.
Posted
Updated 9-Dec-13 2:03am
v2

1 solution

That's because XLS was a proprietary binary format and not XML compatible.
XML compatibility was introduced in Excel 2007 with the extension XLSX.

You can't open XLS files with an XML reader.
 
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