Friday, 11 March 2011

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;[email protected];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 !

No comments:

Post a Comment

Dear Readers, Please post your valuable feedback in the comment section if you like this blog or if you have any suggestions. I would love to hear the same from you. Thanks