Import Data from Excel to SQL Server

 

Import Data from Excel  to SQL Server

 

Next I am going to show how you could import data from your Excel Sheet to Your SQL Server Database on button click. You can modify your code accordingly based upon your requirement ie., You could upload your excel file using fileupload or you could associate new features to this technique.

Design Part:

    <div>

        <asp:Button ID="Button1" runat="server" Text="Import To SQL" OnClick="Button1_Click" />

        <br />

        <br />

        <asp:Label ID="Label1" runat="server" ForeColor="Red"></asp:Label></div>

CodeBehind Part:

Include these namespaces:

using System.Xml;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.IO;

Note(Important): 

1>You have to change the configuration in your connection string according to your database..

2>Here i am using three fields namely Name,Marks1 and Marks2 in my excel file.. You could change your fields accordingly.

Then use the following code snippet:

    protected void Button1_Click(object sender, EventArgs e)

    {

        string filepath = "G:\\Demos\\Practice@Tata\\Demo2\\Excel\\Markt.xlsx";

        string version = Path.GetExtension(filepath);

        //string version = filepath.Substring(filepath.LastIndexOf("."), 3);

        try {

            if ((version == ".xls"))

            {

                // Execute when MS EXCEL 2003 Format

                System.Data.OleDb.OleDbConnection MyConnection;

                System.Data.DataSet DtSet;

                System.Data.OleDb.OleDbDataAdapter MyCommand;

                MyConnection = new System.Data.OleDb.OleDbConnection(("provider=Microsoft.Jet.OLEDB.4.0;"+"Data Source="+filepath+";+Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;"));

                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);

                DtSet = new System.Data.DataSet();

                MyCommand.Fill(DtSet, "[Sheet1$]");

                DataTable dt = DtSet.Tables[0];

                MyConnection.Close();

                if ((dt.Rows.Count > 0))

                {

                for (int i = 0; (i <= (dt.Rows.Count - 1)); i++)

                   {

                DataRow dr = dt.Rows[i];

                string fetch = "insert into StudentList(Name,Marks1,Marks2) values(";

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[0] + "\',")));

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[1] + "\',")));

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[2] + "\')")));

                string connectionString="Data Source=HP-PC\\SQLEXPRESS;Initial Catalog=*****;User ID=*****;pwd=*****";

                SqlConnection sqlcon = new SqlConnection(connectionString);

                sqlcon.Open();

                SqlCommand sqlcmd = new SqlCommand(fetch, sqlcon);

                sqlcmd.CommandType = CommandType.Text;

                sqlcmd.ExecuteNonQuery();

                fetch = "";

                Label1.Text = "successfully Imported values to SQL Server";

                   }

                }

            }

            else if ((version == ".xlsx")) {

                // Execute when MS EXCEL 2007 Format

                System.Data.OleDb.OleDbConnection MyConnection;

                System.Data.DataSet DtSet;

                System.Data.OleDb.OleDbDataAdapter MyCommand;

                MyConnection = new System.Data.OleDb.OleDbConnection(("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\'"

                                + (filepath + "\';Extended Properties=Excel 12.0;")));

                MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);

                DtSet = new System.Data.DataSet();

                MyCommand.Fill(DtSet, "[Sheet1$]");

                DataTable dt = DtSet.Tables[0];

                MyConnection.Close();

                if ((dt.Rows.Count > 0))

                {

                for (int i = 0; (i <= (dt.Rows.Count - 1)); i++)

                   {

                DataRow dr = dt.Rows[i];

                string fetch = "insert into StudentList(Name,Marks1,Marks2) values(";

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[0] + "\',")));

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[1] + "\',")));

                fetch = (fetch + ("\'"

                            + (dt.Rows[i].ItemArray[2] + "\')")));

                string connectionString="Data Source=HP-PC\\SQLEXPRESS;Initial Catalog=*****;User ID=*****;pwd=*****";

                SqlConnection sqlcon = new SqlConnection(connectionString);

                sqlcon.Open();

                SqlCommand sqlcmd = new SqlCommand(fetch, sqlcon);

                sqlcmd.CommandType = CommandType.Text;

                sqlcmd.ExecuteNonQuery();

                fetch = "";

                Label1.Text = "successfully Imported values to SQL Server";

                   }

                }

            }

            else

            {

                Label1.Text = "This Version is not Support!";

            }               

        }

        catch(Exception ex)

        {

        Label1.Text=ex.Message;

        }