How to insert values in SQL from Excel sheet

Question

Hello all I am trying to add values in SQL 2005 particular table from excel sheet containing same column names as table. How to read the Excel values?? Help me out. Regards Zuber Kazi

Jignesh Trivedi · Answer

hi please refer http://www.codeproject.com/Tips/323427/Uploading-Zip-File-in-Asp-net http://www.codeproject.com/Articles/16210/Excel-Reader hope this help.

Satyapriya Nayak · Answer

Hi Zuber Try this... Untitled Page .style1 { width: 50%; } Insert Data View Data View Filter Data View Duplicate Data Delete Data Text=Export to Excel /> using System; 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.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text; public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; protected void insertdata_Click(object sender EventArgs e) { OleDbConnection conn = new OleDbConnection(@Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(test.xls) + ;Extended Properties=Excel 8.0); OleDbCommand com = new OleDbCommand(select * from [Sheet1$] conn); conn.Open(); OleDbDataReader reader = com.ExecuteReader(); string Name = ; string Address = ; string salary = ; while (reader.Read()) { Name = valid(reader 0); Address = valid(reader 1); salary = valid(reader 2); insertdataintosql(Name Address salary); } conn.Close(); l1.Text = Records Inserted successfully; } protected string valid(OleDbDataReader reader int str1) { object val = reader[str1]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string Name string Address string salary) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = insert into employee(NameAddresssalary)values(@Name@Address@salary); com.Parameters.Add(@Name SqlDbType.NVarChar).Value = Name; com.Parameters.Add(@Address SqlDbType.NVarChar).Value = Address; com.Parameters.Add(@salary SqlDbType.Int).Value = Convert.ToInt32(salary); com.CommandType = CommandType.Text; conn.Open(); com.ExecuteNonQuery(); conn.Close(); } protected void viewdata_Click(object sender EventArgs e) { l1.Text =; SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sqlda = new SqlDataAdapter(SELECT * from employee conn); DataSet ds = new DataSet(); sqlda.Fill(dsemployee); GridView1.DataSource = ds; GridView1.DataMember = employee; GridView1.DataBind(); } protected void viewfilterdata_Click(object sender EventArgs e) { l1.Text = ; SqlConnection conn = new SqlConnection(connStr); //SqlDataAdapter sqlda = new SqlDataAdapter(SELECT salary FROM employee GROUP BY salary conn); SqlDataAdapter sqlda = new SqlDataAdapter(SELECT Name FROM employee GROUP BY Name conn); DataSet ds = new DataSet(); sqlda.Fill(ds employee); GridView1.DataSource = ds; GridView1.DataMember = employee; GridView1.DataBind(); } protected void viewduplicatedata_Click(object sender EventArgs e) { l1.Text = ; SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sqlda = new SqlDataAdapter(SELECT Name COUNT(*) AS 'Count'FROM employee GROUP BY Name conn); DataSet ds = new DataSet(); sqlda.Fill(ds employee); GridView1.DataSource = ds; GridView1.DataMember = employee; GridView1.DataBind(); } protected void Button1_Click(object sender EventArgs e) { ExportToExcel(Report.xls GridView1); } private void ExportToExcel(string strFileName GridView dg) { Response.Clear(); Response.Buffer = true; Response.ContentType = application/vnd.ms-excel; Response.Charset = ; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); GridView1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } protected void deletedata_Click(object sender EventArgs e) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; //cmd.CommandText = delete from emp; com.CommandText = truncate table employee; com.CommandType = CommandType.Text; conn.Open(); com.ExecuteScalar(); conn.Close(); l1.Text = Records Deleted successfully; } } Thanks If this post helps you mark it as answer