Help Regarding Insert data from Excel to SqlServer

gokilavasan m

14y4.2k

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Accepted answer
Hi Gokilavasan,

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._Default" %>

    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(ItemCode,SerialNo,Status,CreatedDate,CreatedBy) 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
gokilavasan m14y ago
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         SalesArea,STPNo,SABSiteName,NationalGroup,GroupStore,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 m14y ago
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 sqlserver,I 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 m14y ago
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 sqlserver,I 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 m14y ago
very thank you .It is very useful for me
gokilavasan m14y ago
I need Web Application.

I already sent you stock.rar file.I want same columns in ItemSerialInfo etc

Thanks & Regards,
Gokilavasan.M
Satyapriya Nayak14y ago
Hi Gokilavasan,

You need windows app or web app.

What are the columns to insert into database.

Thanks