Thursday, January 15, 2009

Import excel data into SQL Server table using ASP.NET


string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

using (OleDbConnection connection = new OleDbConnection(xConnStr))
{

OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();


// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String

string sqlConnectionString =DataAccess.GetConnectionString() ;

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{

bulkCopy.DestinationTableName = "dbo.ExcelTest";
bulkCopy.WriteToServer(dr);
}
}
}

No comments:

Post a Comment

Highly Appreciated your comments