Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASPNet using C and VBNet

In this article I will explain with an example, how to import Excel file data into SQL Server database using SqlBulkCopy in ASP.Net using C# and VB.Net.

Download System.Data.OleDb DLL

There are two ways you can download the System.Data.OleDb DLL.

MSI Installer:

Download Microsoft.ACE.OLEDB.12.0 provider for 32bit and 64bit

Nuget:

Install System.Data.OleDb Nuget Package

Connection Strings

The first thing is to build connection strings to Excel files and Excel files are broadly divided into two types and Excel 97-2003 and Excel 2007 and higher.

Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.

Excel 2007 and higher format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.

Database

I have made use of the following table Customers with the schema as follow.

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

HTML Markup

The HTML Markup consists of following controls:

FileUpload – For uploading excel file.

Button – For importing Excel file into database.

The Button has been assigned with an OnClick event handler.

<asp:FileUpload ID="fuUpload" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Import" OnClick="OnImport" />

Namespaces

You will need to import the following namespaces.

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

Reading and Importing Excel file into Database using C# and VB.Net

First a check is performed if the file is selected or not, if selected then the selected file is saved into a Folder (Directory) named Uploads.

Then, using the switch case statement, the Excel file extension is determined and the connection string is built which is fetched from the Web.Config file.

Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.

A connection is established with the Excel file using OLEDB classes and the name of the first sheet is determined and the first sheet data is read into a DataTable.

Now a connection is established with the database and the SqlBulkCopy object is initialized and name of the Table is specified using DestinationTableName property.

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.

protected void OnImport(object sender, EventArgs e)

{

string filePath = string.Empty;

if (fuUpload.PostedFile != null)

{

string path = Server.MapPath("~/Uploads/");

if (!Directory.Exists(path))

{

Directory.CreateDirectory(path);

}

filePath = path + Path.GetFileName(fuUpload.PostedFile.FileName);

string extension = Path.GetExtension(fuUpload.PostedFile.FileName);

fuUpload.PostedFile.SaveAs(filePath);

string constr = string.Empty;

switch (extension)

{

case ".xls"://Excel 97-03.

constr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

break;

case ".xlsx"://Excel 07 and above.

constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

break;

}

DataTable dt = new DataTable();

constr = string.Format(constr, filePath);

using (OleDbConnection connExcel = new OleDbConnection(constr))

{

using (OleDbCommand cmdExcel = new OleDbCommand())

{

using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())

{

cmdExcel.Connection = connExcel;

//Get the name of First Sheet.

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

connExcel.Close();

//Read Data from First Sheet.

connExcel.Open();

cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";

odaExcel.SelectCommand = cmdExcel;

odaExcel.Fill(dt);

connExcel.Close();

}

constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

{

//Set the database table name.

sqlBulkCopy.DestinationTableName = "dbo.Customers";

//[OPTIONAL]: Map the Excel columns with that of the database table

sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId");

sqlBulkCopy.ColumnMappings.Add("Name", "Name");

sqlBulkCopy.ColumnMappings.Add("Country", "Country");

con.Open();

sqlBulkCopy.WriteToServer(dt);

con.Close();

}

VB.Net

Protected Sub OnImport(ByVal sender As Object, ByVal e As EventArgs)

Dim filePath As String = String.Empty

If fuUpload.PostedFile IsNot Nothing Then

Dim excelPath As String = Server.MapPath("~/Uploads/")

If Not Directory.Exists(excelPath) Then

Directory.CreateDirectory(excelPath)

End If

filePath = excelPath + Path.GetFileName(fuUpload.PostedFile.FileName)

Dim extension As String = path.GetExtension(fuUpload.PostedFile.FileName)

fuUpload.PostedFile.SaveAs(filePath)

Dim constr As String = String.Empty

Select Case extension

Case ".xls" 'Excel 97-03.

constr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString

Case ".xlsx" 'Excel 07 and above.

constr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString

End Select

Dim dt As DataTable = New DataTable()

constr = String.Format(constr, filePath)

Using connExcel As OleDbConnection = New OleDbConnection(constr)

Using cmdExcel As OleDbCommand = New OleDbCommand()

Using odaExcel As OleDbDataAdapter = New OleDbDataAdapter()

cmdExcel.Connection = connExcel

'Get the name of First Sheet.

connExcel.Open()

Dim dtExcelSchema As DataTable

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

Dim sheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

connExcel.Close()

'Read Data from First Sheet.

connExcel.Open()

cmdExcel.CommandText = "SELECT * From [" & sheetName & "]"

odaExcel.SelectCommand = cmdExcel

odaExcel.Fill(dt)

connExcel.Close()

End Using

constr = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As SqlConnection = New SqlConnection(constr)

Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)

'Set the database table name.

sqlBulkCopy.DestinationTableName = "dbo.Customers"

'[OPTIONAL]: Map the Excel columns with that of the database table

sqlBulkCopy.ColumnMappings.Add("Customer Id", "CustomerId")

sqlBulkCopy.ColumnMappings.Add("Name", "Name")

sqlBulkCopy.ColumnMappings.Add("Country", "Country")

con.Open()

sqlBulkCopy.WriteToServer(dt)

con.Close()

End Using

End If

End Sub

Screenshots

The Excel File

Table containing the data from the Excel file

Downloads

Download Code

Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net

Related