In this article I will explain with an example, how to insert data from Excel sheet into SQL Server database table using SqlBulkCopy in C# and VB.Net.
SqlBulkCopy class as the name suggests does bulk insert from one source to another and hence all rows from the Excel sheet can be easily read and inserted using the SqlBulkCopy class.
Database
I have created a simple table named tblPersons whose schema is shown below.
![Using SqlBulkCopy to import excel spreadsheet data into SQL Server in ASP.Net using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download SQL file
Connection String to the Database
<add name="constr" connectionString="Data Source=.\SQL2008R2;Database=PersonsDB;User Id=mudassar;password=xxxxx"/>
Connection String for Excel 2003 and Excel 2007 or higher formats
The Connection Strings for the Excel files of both 2003 and 2007 or higher formats have been specified in the Web.Config file.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
HTML Markup
The HTML Markup consists of ASP.Net FileUpload control and a Button to trigger the file upload process.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Uploading and inserting rows from Excel to SQL Server Table
On the click of the button the Excel file is first uploaded and then saved inside a folder named Files.
Then based on the extension of the Excel file i.e. xls or xlsx, the appropriate connection sting is fetched from the Web.Config file.
Then a connection is established with the Excel file and the name of the first sheet is read into a variable.
Note: I am considering all Excel files with the first row as the Header Row containing the names of the columns, you can set HDR=’No’ if your excel file does not have a Header Row.
Using the fetched Sheet name, a SELECT statement is executed and all the records from the Excel sheet are fetched into a DataTable.
Note: You will notice I have defined columns in DataTable before loading data from Excel file, though it is not necessary but recommended as otherwise all fields will be considered of type string by default.
Now a connection is established with the database and the SqlBulkCopy object is initialized and I have specified the name of the Table using the DestinationTableName property.
Finally the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
Note: The mapping of columns of the DataTable and the SQL Server table is optional and you need to do only in case where your DataTable and/or the SQL Server Table do not have same number of columns or the names of columns are different.
C#
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Salary", typeof(decimal)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPersons";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
VB.Net
Protected Sub Upload(sender As Object, e As EventArgs)
'Upload and save the file
Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)
Dim connString As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
Case ".xls"
'Excel 97-03
connString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Exit Select
Case ".xlsx"
'Excel 07 or higher
connString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
Exit Select
End Select
connString = String.Format(connString, excelPath)
Using excel_con As New OleDbConnection(connString)
excel_con.Open()
Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
Dim dtExcelData As New DataTable()
'[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), _
New DataColumn("Name", GetType(String)), _
New DataColumn("Salary", GetType(Decimal))})
Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con)
oda.Fill(dtExcelData)
End Using
excel_con.Close()
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(conString)
Using sqlBulkCopy As New SqlBulkCopy(con)
'Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPersons"
'[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "PersonId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary")
con.Open()
sqlBulkCopy.WriteToServer(dtExcelData)
con.Close()
End Using
End Using
End Using
End Sub
Screenshots
Excel File
![Using SqlBulkCopy to import excel spreadsheet data into SQL Server in ASP.Net using C# and VB.Net]()
Table containing the data from the Excel file
![Using SqlBulkCopy to import excel spreadsheet data into SQL Server in ASP.Net using C# and VB.Net]()
![Using SqlBulkCopy to import excel spreadsheet data into SQL Server in ASP.Net using C# and VB.Net]()
Downloads
