Help Regarding Insert data from Excel to SqlServer

Question

I want to Insert data from Excel to SqlServer.I want to Insert Thousand's of Insert Queries.It is not Possible to write a insert query for Thousand Rows.Please give me the Excel Query to write the Insert Command.I will give you the Excel sheet.

Thanks & Regards,
Gokilavasan.M

Satyapriya Nayak · Answer

Hi Gokilavasan 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

Satyapriya Nayak · Answer

Hi Gokilavasan You need windows app or web app. What are the columns to insert into database. Thanks

gokilavasan m · Answer

Hi Satyapriya Nayak I want (webApplication) to Insert data from SqlServer (using StoredProcedure) to Excel. I have One Excel File AdhocCostsMay2012.xls. That Excel(AdhocCostsMay2012.xls) File have SalesAreaSTPNoSABSiteNameNationalGroupGroupStore PrivateIncVAT Region Columns.Totaly 546 Rows Available.All Columns have values.But PrivateIncVAT Column does not have values. I want to pull PrivateIncVAT Column values from SqlServer using StoredProcedure (Note-I Attached that Excel (AdhocCostsMay2012.xls) File) And I am using one storedProcedure name as sp_AdochCostSABCostperOutletBySTPNo (Note-I Attached that storedProcedure ( sp_AdochCostSABCostperOutletBySTPNo ) . In my stored procedure have 602 Columns varies each month. Finally i want pull values from stored procedure and fill that PrivateIncVAT column. and also i attached while executing stored procedure values . Thanks & Regards Gokilavasan.M

gokilavasan m · Answer

Hi Satyapriya Nayak I want to Insert data from SqlServer to Excel using visual studio windows application(using c# language) and sqlserver 2005.In my sqlserverI have stock table with the columns.the stock table rows increased every day depending upon stock items.I want to give startdate and enddate using datetimepicker control in windows app.when i click the submit button means it will show the records in excel.what can i do.please help me. I have attached my excel sheet.please find that. Thanks & Regards Gokilavasan.M

gokilavasan m · Answer

Hi Satyapriya Nayak I want to Insert data from SqlServer to Excel using visual studio windows application(using c# language) and sqlserver 2005.In my sqlserverI have stock table with the columns.the stock table rows increased every day depending upon stock items.I want to give startdate and enddate using datetimepicker control in windows app.when i click the submit button means it will show the records in excel.what can i do.please help me. I have attached my excel sheet.please find that. Thanks & Regards Gokilavasan.M

gokilavasan m · Answer

very thank you .It is very useful for me

gokilavasan m · Answer

I need Web Application. I already sent you stock.rar file.I want same columns in ItemSerialInfo etc Thanks & Regards Gokilavasan.M