title: how to import the data from excel sheet into sql server table?
body: <p>Hi. I want to import the excel sheet data to SQL server table,for that I wrote code like as below. The sheet is uploading into folder,but the data is not imported to table,can anyone  tell me please where is the mistake....</p>
  
  <pre><code>public partial class upload2 : System.Web.UI.Page
      {
          private string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
  
          protected void Page_Load(object sender, EventArgs e)
          {
              if (!IsPostBack)
              {
                  PopulateDatabaseTables();
              }
          }
  
          private void PopulateDatabaseTables()
          {
              string tableName = string.Empty;
              string sql = "SELECT *, name AS table_name " +
                  " FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
              using (SqlConnection conn = new SqlConnection(connStr))
              {
                  using (DataTable table = new DataTable())
                  {
                      conn.Open();
                      using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
                      {
                          dAd.Fill(table);
                      }
                      ListBox1.DataSource = table;
                      ListBox1.DataBind();
                  }
              }
          }
  
          protected void ImportNow_Click(object sender, EventArgs e)
          {
              if (ListBox1.SelectedValue == "")
              {
                  lblMessage.ForeColor = Color.Red;
                  lblMessage.Text = "Please select table in which you want to import data from excel sheet";
              }
              else if ((fileuploadExcel.FileName != ""))
              {
                  string extension = Path.GetExtension(fileuploadExcel.PostedFile.FileName);
  
                  string excelConnectionString;
                  SqlConnection conn = new SqlConnection(connStr);
                  string tableName = ListBox1.SelectedValue;
                 // string path = fileuploadExcel.PostedFile.FileName;
                  string path = Server.MapPath("~/fileuploadExcel/" + fileuploadExcel.FileName);
                  fileuploadExcel.SaveAs(path);
                  Response.Write("path=" + path);
                  return;
                  //Create connection string to Excel work book
                  if (extension == ".xlsx")
                  {
                      excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+ path +
                                                            ;Extended Properties=Excel 8.0;Persist Security Info=False";
                  }
                  else
                  {
                      excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;sData Source= + path +
                                                           ;Extended Properties=Excel 12.0;Persist Security Info=False";
                  }
  
                  //Create Connection to Excel work book
                  SqlConnection excelConnection = new SqlConnection(excelConnectionString);
                  //Create OleDbCommand to fetch data from Excel             
                  conn.Open();
                  SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
                  SqlCommand identityChange = conn.CreateCommand();
                  identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
                  SqlCommand cmd = new SqlCommand("Select * from [Sheet1$]", excelConnection);
                  excelConnection.Open();
                  SqlDataReader dReader;
                  dReader = cmd.ExecuteReader();
                  identityChange.ExecuteNonQuery();
                  SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr);
                  //Give your Destination table name
                  sqlBulk.DestinationTableName = tableName;
                  sqlBulk.WriteToServer(dReader);
                  excelConnection.Close();
                  conn.Close();
                  lblMessage.ForeColor = Color.Green;
                  lblMessage.Text = "Import into table &lt;b&gt;" + tableName + "&lt;/b&gt; successful!&lt;br /&gt;";
              }
              else
              {
                  lblMessage.ForeColor = Color.Red;
                  lblMessage.Text = "Please first upload (Select) excel file.";
              }
          }
  
          protected void viewdata_Click(object sender, EventArgs e)
          {
              BindData();
          }
  
          private void BindData()
          {
              try
              {
                  if (ListBox1.SelectedValue == "")
                  {
                      lblMessage.ForeColor = Color.Red;
                      lblMessage.Text = "Please select table for which you want to view data in Gridview";
                  }
                  else
                  {
                      string tableName = ListBox1.SelectedValue;
                      SqlConnection conn = new SqlConnection(connStr);
                      SqlDataAdapter sda = new SqlDataAdapter("select * from " + tableName, conn);
                      DataSet ds = new DataSet();
                      sda.Fill(ds);
                      gvdetails.DataSource = ds;
                      gvdetails.DataBind();
                  }
              }
              catch (DataException de)
              {
                  lblMessage.Text = de.Message;
                  lblMessage.ForeColor = System.Drawing.Color.Red;
              }
          }
  
          protected void gvdetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
          {
              gvdetails.PageIndex = e.NewPageIndex;
              BindData();
          }
      }
  }
  </code></pre>