Bulk copy data from excel sheet to sql table

How to process bulk copy data from excel sheet to sql table. Here is code to copy data from excel to sql.

      C# Code:
       string excelFile = @"C:\Book1.xls";
        string ssqlTable = "[MyTableName]";
        string exceldataQuery = "select [id],[email],[status] from [Sheet1$]";
        try
        {
            string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFile +
            ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
            string ssqlconnectionstring = "Data Source=dbserver;Initial Catalog=Practice;User ID=sa;Password=@password;connection timeout=54000;Max Pool Size=300;Asynchronous Processing=true;";
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(exceldataQuery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqlTable;
            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);
            }

            oledbconn.Close();
            Response.Write("Completed");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

Enjoy !

Comments

Popular posts from this blog

CAML Query in SharePoint 2010

Calling REST APi from server side code - C#

All about SharePoint 2010 Content Type Hub

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX