how to copy the data from excel to sqlserver

Question

how to copy the data from excel to sqlserver please tell me. Thanks Regards Narasiman P

Satyapriya Nayak · Answer

Hi Narasiman Try this... Untitled Page Text=Upload Excel Files OnClick=ButtonUpload_Click /> Text=View Excel Data OnClick=ButtonView_Click /> Please select an Excel file to import: Text=Upload File OnClick=ButtonUploadFile_Click /> BorderStyle=None BorderWidth=1px CellPadding=3 CellSpacing=2> Text=Insert to Db /> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb; using System.Data.SqlClient; namespace Show_Excel_gridview_insert_to_db { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; SqlCommand com; protected void ButtonUploadFile_Click(object sender EventArgs e) { if (FileUploadExcel.HasFile) { try { FileUploadExcel.SaveAs(Server.MapPath(~/ExcelImport.xls)); LabelUpload.Text = Upload File Name:  + FileUploadExcel.PostedFile.FileName +   + Type:  + FileUploadExcel.PostedFile.ContentType +  File Size:  + FileUploadExcel.PostedFile.ContentLength +  kb ; } catch (System.NullReferenceException ex) { LabelUpload.Text = Error:  + ex.Message; } } else { LabelUpload.Text = Please select a file to upload.; } } protected OleDbCommand ExcelConnection() { string ConnStr = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source= + Server.MapPath(ExcelImport.xls) + ; + Extended Properties=Excel 8.0;; OleDbConnection Con = new OleDbConnection(ConnStr); Con.Open(); OleDbCommand com = new OleDbCommand(SELECT * FROM [sheet1$] Con); return com; } protected void ButtonView_Click(object sender System.EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = true; PanelImport.Visible = false; OleDbDataAdapter oledbda = new OleDbDataAdapter(); oledbda.SelectCommand = ExcelConnection(); DataSet ds = new DataSet(); oledbda.Fill(ds); //GridViewExcel.DataSource = ds.Tables[0].DefaultView; GridViewExcel.DataSource = ds; GridViewExcel.DataBind(); } protected void ButtonUpload_Click(object sender System.EventArgs e) { PanelUpload.Visible = true; PanelView.Visible = false; PanelImport.Visible = false; } protected void btn_insert_Click(object sender EventArgs e) { foreach (GridViewRow g1 in GridViewExcel.Rows) { SqlConnection con = new SqlConnection(connStr); com = new SqlCommand(insert into stock(ItemCodeSerialNoStatusCreatedDateCreatedBy) values (' + g1.Cells[0].Text + '' + g1.Cells[1].Text.ToString() + '' + g1.Cells[2].Text + '' + g1.Cells[3].Text + '' + g1.Cells[4].Text + ') con); con.Open(); com.ExecuteNonQuery(); con.Close(); } Label1.Text = Records inserted successfully; } } } Thanks If this post helps you mark it as answer