Errors on loading excel file to Sql using OLEDB

Question

This is absolutely not familiar to me. I am trying to load an excel file using the following method:

protected void Button2_Click(object sender, EventArgs e)
{
    try
    {
        if (FileUpload2.HasFile)
        {
            string path = string.Concat((Server.MapPath("~/members/temp/" + FileUpload2.FileName)));
            FileUpload1.PostedFile.SaveAs(path);
           // OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
            OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0 Xml;HDR=YES;");
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);

            OleDbcon.Open();
            DbDataReader dr = cmd.ExecuteReader();

            string con_str = ConfigurationManager.ConnectionStrings["BOMConnectionString"].ConnectionString;

            // Bulk Copy to SQL Server 
            SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
            bulkInsert.DestinationTableName = "Table_xxc";
            bulkInsert.WriteToServer(dr);
            OleDbcon.Close();
            Array.ForEach(Directory.GetFiles((Server.MapPath("~/members/temp/"))), File.Delete);
            Label2.ForeColor = Color.Green;
            Label2.Text = "successfully inserted";

        }
        else
        {
            Label2.ForeColor = Color.Red;
            Label2.Text = "Please select the File";
        }
    }
    catch (Exception ex )
    {

        Response.Write("<script>alert(\"An error occurred. The file cannot be loaded to the DB. Check your formats in the excel file.\")</script>"); Response.Write("<script>alert(\"An error occurred. The file cannot be loaded to the DB. Check your formats in the excel file.\")</script>");
    }
}

The excel file and the SQL table are formatted as following:

  **Field   Excel  SQl**
-"Code"= Number-Float
-"Description"=Text-NvarChar(MAX)
-"Unit"=Text-NvarChar(50)
-"Cost"=Number-Float
-"Note"=Text-NvarChar(MAX)

If I use the import/export features from Sql server Management Studio it works fine. If I use the above snippet I get errors on the line "OleDbcon.Open()". Thai is the Stack trace:

in System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
in System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
in System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
in System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
in System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
in System.Data.OleDb.OleDbConnection.Open()
in Material.Button2_Click(Object sender, EventArgs e) in c:\Users\Pink\Documents\Visual Studio 2012\Projects\^^WebSiteTemplateAccess - BOM\members\Training_Area.aspx.cs:riga 91
in System.Web.UI.WebControls.Button.OnClick(EventArgs e)
in System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
in System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
in System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
in System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
in System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

The excel file is an xml from Excel 2007 but I have also tried excel 2010 and as you can see in my code I have used the appropriate OLEDB driver. I always get "External table is not in the expected format." I need some help to get this right. Is there anything wrong in the above code/formats?

If you get exceptions than why not include the full details in your question as well as indicating which lines they occur on? — Daniel Kelley, Jun 14 '14 at 16:00
@DanielKelley, sorry for that Daniel. I have modified the question and added the details. — FeliceM, Jun 14 '14 at 16:08
This question may help - http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format — Daniel Kelley, Jun 14 '14 at 16:09
@DanielKelley, thanks. Intersting material. I did not find it when I was searching. I will try to get something out of it. — FeliceM, Jun 14 '14 at 16:14

Errors on loading excel file to Sql using OLEDB

0 Answers0