How to import bulk data from excel sheet to SQL Server?
In this article I am going to explain about how to insert bulk data from excel in to SQL server table. This code snippet is may be help you in future to import data from excel.
Description :
I have import data from excel to sql server using SqlBulkCopy class Instead of writing loop to retrieve and insert this one is quickly insert into sql server table.
First I have entered some values in the excel sheet and save it in some location and then I have created table like below structure
CREATE TABLE [dbo].[emp](
[eno] [int] NULL,
[empname] [varchar](50) NULL,
[sal] [bigint] NULL
)Client side
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Bulk Insert data</title>
</head>
<body>
<form id="form1" runat="server">
<table width="800" align="center" cellpadding="0" cellspacing="0">
<tr>
<td colspan="2" align="center" height="60">
<b>Bulk Insert of Excel Sheet data into SQL SERVER</b>
</td>
</tr>
<tr>
<td colspan="2" align="center" height="30">
<asp:Label ID="lblmsg" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td height="60">
Select your File
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
</tr>
<tr>
<td colspan="2" align="center" height="60">
<asp:Button ID="Button1" runat="server" Text="Click to Bulk Upload Excel Data" OnClick="Button1_Click" />
</td>
</tr>
<tr>
<td colspan="2" align="center" height="60">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>
</tr>
</table>
</form>
</body>
</html>
In the client side I collect excel sheet from user using file upload control and I have designed client side like below. 
Server side
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
lblmsg.Text = "";
LoadGrid();
}
void LoadGrid()
{
sqlcon.Open();
sqlcmd = new SqlCommand("select * from emp", sqlcon);
da = new SqlDataAdapter(sqlcmd);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
sqlcon.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string filename = FileUpload1.FileName;
FileUpload1.SaveAs(Server.MapPath(filename));
ExportToSQLServer(Server.MapPath(filename));
}
}
void ExportToSQLServer(string path)
{
try
{
//Connection string for Excel data source MS EXCEL only 2003 supported connection string
//string excelconstr = "provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;";
//Connection string for Excel data source MS EXCEL 2007 / 2003
string excelconstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;";
//Create instance of oledb connection
OleDbConnection excelcon = new OleDbConnection(excelconstr);
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelcon);
//Open excel connection
excelcon.Open();
//Create instrance for oledb Data Reader
OleDbDataReader dReader;
//Assign oledb command for data reader
dReader = cmd.ExecuteReader();
//Create instance for SQL bulk copy
SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["Con"].ToString());
//Assign destination sql server target table name below
sqlBulk.DestinationTableName = "emp";
//Write to server table
sqlBulk.WriteToServer(dReader);
//Close excel connectio nafter processed
excelcon.Close();
//Display success message
lblmsg.Text = "Excel data upload successfully";
}
catch (Exception ex)
{
lblmsg.Text = ex.ToString();
}
//Delete after read data from server path
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
LoadGrid();
}
}Output :
Source code:
Client Side: ASP.NET
Code Behind: C#Conclusion
I hope this code snippet is helping you to know about import bulk data from excel sheet into sql server.