Posts

Showing posts from 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;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();
         …