In this article I will explain with an example, how to import Excel data to SQL Server Database in ASP.Net using C# and VB.Net.
The Excel Sheet data will be read into a DataTable and then then DataTable data will be imported into SQL Server Database using SqlBulkCopy in ASP.Net.
 
 
Database
I have created a simple table named tblPersons whose schema is shown below.
Import Excel data to SQL Server Database 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
 
 
Importing Excel data to SQL Server Database
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
Import Excel data to SQL Server Database in ASP.Net using C# and VB.Net
 
Table containing the data from the Excel file
Import Excel data to SQL Server Database in ASP.Net using C# and VB.Net
 
If you are getting the following error then refer my article The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Import Excel data to SQL Server Database in ASP.Net using C# and VB.Net
 
 
Downloads
Download Code