1

I am trying to save the data in sql database from an xls format file.i have tried the below code but it shows error that External table is not in the expected format.can anyone help me how i can save the data from an xls format file into database?

code:
 protected void Button1_Click(object sender, EventArgs e)
    {
        string sheet1 = "asdf";
        string path = MapPath("~/dataWorldcup/asdf.xls");
        OleDbConnection oconn1 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
        OleDbCommand ocmd = new OleDbCommand("select * from [" + sheet1 + "$]", oconn1);
        SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["consed"].ConnectionString);
        oconn1.Open();
        sqlcon.Open();
        OleDbDataReader odr = ocmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlcon);

        sqlBulk.DestinationTableName = "[Table]";
        sqlBulk.WriteToServer(odr);
        sqlcon.Close();
        oconn1.Close();
    }

Error : enter image description here

Database Table:

enter image description here

excel file:- asdf.xls enter image description here

Can Anyone help me with this?It will be Great Help.

Avinash
  • 193
  • 1
  • 14
  • Seriously this is not a duplicate question at all. the correct answer on the one you give as a duplicate answer doesn't even give an exact answer to this. look at the comments. The answer to this one may be related to the connection string but in all probability their connection string is already correct (check the question!). The problem appears to be related to a schema mismatch between the range in excel and the sql table. The size and schema should match EXACTLY otherwise you'll run into exceptions. – Anonymous Type Sep 16 '14 at 06:00

1 Answers1

0

My initial guess is that you are not using the correct connection string for the version of Excel sheet you have. This is my guess because you are not even doing anything when the error occurs except attempt to connect to the sheet.

Try to search for the correct connection string.

EDIT:

Try Excel 8.0; instead of Excel 12.0?

Lzh
  • 3,585
  • 1
  • 22
  • 36