In this article I will explain with an example, how to insert data from Excel sheet into SQL Server database table in ASP.Net using C# and VB.Net.
The Excel file will be first uploaded and copied to a folder on Server. Then, using Stored Procedure and OLEDB, the data from Excel file will be read and imported into SQL Server Database Table.
 
 
Database
I have created a simple table named tblPersons whose schema is shown below.
Read and Import Excel Sheet into SQL Server Database in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 
Stored Procedures for importing Excel data
Excel 97 – 2003 Format
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_ImportFromExcel03
    @SheetName varchar(20),
    @FilePath varchar(100),
    @HDR varchar(3),
    @TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
           
    IF OBJECT_ID (@TableName,'U') IS NOT NULL
      SET @SQL =z'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
 
    SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL
END
GO
 
Excel 2007 and higher format
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_ImportFromExcel07
   @SheetName varchar(20),
   @FilePath varchar(100),
   @HDR varchar(3),
   @TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
     
    IF OBJECT_ID (@TableName,'U') IS NOT NULL
      SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
    ELSE
      SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
 
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='
    SET @SQL = @SQL + @HDR + ''''''')...['
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL
END
GO
 
 
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:
FileUpload – For selecting file to upload.
Button – For importing file into SQL Server database.
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
When Upload button is clicked, the uploaded Excel file is saved to a folder named Files and based on the extension of the Excel file i.e. xls or xlsx, the appropriate connection sting is fetched from the Web.Config file.
An object of OleDbConnection class is created and 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.
 
After that based on the extension of the Excel file i.e. xls or xlsx, the appropriate Stored Procedure is executed using ExecuteNonQuery method and the following parameters are passed.
Note: For more details on how to use ExecuteNonQuery function, please refer Understanding SqlCommand ExecuteNonQuery in C# and VB.Net.
 
SheetName – The name of the Excel Sheet to be read.
FilePath – The complete Path of the Excel file.
HDR – Value YES if your Excel file has Header Row else NO.
TableName – Name of the Table in SQL Server Database.
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 storedProc = string.Empty;
    string sheet1 = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls": //Excel 97-03.
            storedProc = "spx_ImportFromExcel03";
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 or higher.
            storedProc = "spx_ImportFromExcel07";
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;
 
    }
 
    //Read the Sheet Name.
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        excel_con.Close();
    }
 
    //Call the Stored Procedure to import Excel data in Table.
    string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(storedProc, con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SheetName", sheet1);
            cmd.Parameters.AddWithValue("@FilePath", excelPath);
            cmd.Parameters.AddWithValue("@HDR", "YES");
            cmd.Parameters.AddWithValue("@TableName", "tblPersons");
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
 
VB.Net
Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    'Upload and save the File.
    Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(excelPath)
    Dim conString As String = String.Empty
    Dim storedProc As String = String.Empty
    Dim sheet1 As String = String.Empty
    Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
 
    Select Case extension
        Case ".xls" 'Excel 97-03.
            storedProc = "spx_ImportFromExcel03"
            conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
        Case ".xlsx" 'Excel 07 or higher.
            storedProc = "spx_ImportFromExcel07"
            conString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
    End Select
 
    'Read the Sheet Name.
    conString = String.Format(conString, excelPath)
    Using excel_con As OleDbConnection = New OleDbConnection(conString)
        excel_con.Open()
        sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
        excel_con.Close()
    End Using
 
    'Call the Stored Procedure to import Excel data in Table.
    Dim constr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(storedProc, con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@SheetName", sheet1)
            cmd.Parameters.AddWithValue("@FilePath", excelPath)
            cmd.Parameters.AddWithValue("@HDR", "YES")
            cmd.Parameters.AddWithValue("@TableName", "tblPersons")
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub
 
 
Possible Error
The following error occurs when you make use of Excel 2007 or higher or Access 2007 or higher files using OLEDB connection in C# or VB.Net.
Read and Import Excel Sheet into SQL Server Database in ASP.Net
 
 
Solution
The solution to this problem is explained in this article, The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
 
 
Screenshots
Excel File
Read and Import Excel Sheet into SQL Server Database in ASP.Net
 
Table containing the data from the Excel file
Read and Import Excel Sheet into SQL Server Database in ASP.Net
 
 
Downloads
Download Code