Monday, July 5, 2010

Insert excel data into DB by using C#.NET

In this example i am going to describe how to Import or insert data into Sql server from Excel spreadsheet using sqlbulkcopy method.

Create a Excel workbook as shown in image below and insert some data into it.

Create a table in SQL database as shown in below

ColumnName Data Type
ID int
Name varchar(50)
Location varchar(50)

Now write this code to insert data into SQL table
public partial class _Default : System.Web.UI.Page
string strConnection = ConfigurationManager.ConnectionStrings
protected void Page_Load(object sender, EventArgs e)

protected void Button1_Click(object sender, EventArgs e)
//Create connection string to Excel work book
string excelConnectionString =
Data Source=C:\Details.xls;
Extended Properties="
"Excel 8.0;HDR=YES;""";

//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select [ID],[Name],[Location] from [Detail$]",

OleDbDataReader dReader;
dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");

If there are more columns in your database table or excel workbook and you want to insert data in some of them than you need to add ColumnMappings like this

sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");

Below i am writing the code without sqlbulk by using dataset.

protected void Button2_Click(object sender, EventArgs e)
DataSet dUser_Info = new DataSet();

string excelConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", "C:\\Details.xls");

string query = String.Format("Select [Id],[Name],[Location] from [Sheet1$]");
OleDbConnection oleCon = new OleDbConnection(excelConnectionString);
OleDbDataAdapter oleAdp = new OleDbDataAdapter(query, oleCon);
catch (OleDbException ex)
if (ex.Message == "No value given for one or more required parameters.")
//paramter name is invalid

SqlConnection DBConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"]);


foreach (DataRow dr in dUser_Info.Tables[0].Rows)
SqlCommand DBQuery = new System.Data.SqlClient.SqlCommand();

DBQuery.Connection = DBConn;

DBQuery.CommandText = "insert into tbuserinfo (Id,Name,Location) values('" + dr["Id"].ToString() + "','" + dr["Name"].ToString() + "','" + dr["Location"].ToString()+"'";






  1. This is a nice article..
    Its easy to understand ..
    And this article is using to learn something about it..

    c#,, php tutorial, Ms sql server

    Thanks a lot..!

  2. Thanks for U r Code... It works Good...
    I 've a doubt ..How to import from many Excel Sheets at a time from a single folder.Here U have mentioned as "Sheet1$" But in above case how to do it... Can U define IT....!!!!!