How to Import Excel Sheet Data into SQL Server Table using ASP.Net C# VB.Net

After developing an web application when you ask the operator to enter all existing data into the input interface then most of the cases the operator told you i have an Excel sheet please upload those data into the database automatically, which will reduce my time as well as human errors. This is the most common scenario. So you have to develop an interface where user can select an excel sheet to import into the SQL SERVER database table.

Fortunately Asp.Net ADO.NET provide us a new feature named SqlBulkCopy which gives you DTS like speed to transfer Excel sheet data into the SQL SERVER table. Here i will show you by an example how you can easily do this. At first upload the Excel Sheet into the web server, choose Excel columns you want to transfer, map columns between Excel Data & SQL SERVER Table, after that start importing. If the Database table does not exist then create one as per your requirement.

Objective/Outcome:
How to Import Excel Sheet Data into SQL Server Table using ASP.Net CSharp VB.Net

To do the Example:
Download the Excel sheetEmployee.xlsx“.

Create The Below Table:

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [bigint] NOT NULL,
	[EmployeeName] [varchar](max) NOT NULL,
	[Designation] [varchar](max) NOT NULL,
	[Posting] [varchar](max) NOT NULL,
	[Dept] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Modify The Web.Config File:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add an Upload Control & Import Button:

    <asp:FileUpload ID="FileUpload1" runat="server" />       
    <asp:Button ID="Button1" runat="server" Text="Import" OnClick="btnUpload_Click" />

Under Import Button Click Event Write Below Code:
C# Code:

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string sPath = Server.MapPath("~/BulkFolder/" + FileUpload1.FileName);
            FileUpload1.SaveAs(sPath);

            ImporttoSQL(sPath);
        }
    }

    private void ImporttoSQL(string sPath)
    {
        // Connect to Excel 2007 earlier version
        //string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
        // Connect to Excel 2007 (and later) files with the Xlsx file extension 
        string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);

        string sDestConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
        using (sSourceConnection)
        {
            string sql = string.Format("Select [Employee ID],[Employee Name],[Designation],[Posting],[Dept] FROM [{0}]", "Sheet1$");
            OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
            sSourceConnection.Open();
            using (OleDbDataReader dr = command.ExecuteReader())
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
                {
                    bulkCopy.DestinationTableName = "Employee";
                    //You can mannualy set the column mapping by the following way.
                    //bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");
                    bulkCopy.WriteToServer(dr);
                }
            }
        }

    }

Note:
1. Don’t forget to add “System.Data.SqlClient”, “System.Data.OleDb”, “System.Configuration” namespaces.
2. Earlier Version of Excel 2007 use below connection string:

string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";

3. For Excel 2007 & Later Version use below connection string:

string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);

4. HDR=Yes indicates that the first row contains column names, not data. HDR=No indicates the opposite.
5. You can manually set the column mapping (if Excel column name differ with SQL Server Database Table column name) by the following way:

bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");

6. Modify the Excel sheet name(Sheet1$) from the SQL as per your Excel sheet name.
7. Add a Folder Named “BulkFolder” in your root directory to keep the uploaded Excel File.

VB.Net Code:

    Protected Sub btnUpload_Click(sender As Object, e As System.EventArgs)
        If FileUpload1.HasFile Then
            Dim sPath As String = Server.MapPath("~/BulkFolder/" + FileUpload1.FileName)
            FileUpload1.SaveAs(sPath)

            ImporttoSQL(sPath)
        End If
    End Sub

    Private Sub ImporttoSQL(sPath As String)
        ' Connect to Excel 2007 earlier version
        'Dim sSourceConstr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;"""
        ' Connect to Excel 2007 (and later) files with the Xlsx file extension 
        Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)

        Dim sDestConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim sSourceConnection As New OleDbConnection(sSourceConstr)
        Using sSourceConnection
            Dim sql As String = String.Format("Select [Employee ID],[Employee Name],[Designation],[Posting],[Dept] FROM [{0}]", "Sheet1$")
            Dim command As New OleDbCommand(sql, sSourceConnection)
            sSourceConnection.Open()
            Using dr As OleDbDataReader = command.ExecuteReader()
                Using bulkCopy As New SqlBulkCopy(sDestConstr)
                    bulkCopy.DestinationTableName = "Employee"
                    'You can mannualy set the column mapping by the following way.
                    'bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");
                    bulkCopy.WriteToServer(dr)
                End Using
            End Using
        End Using
    End Sub

Note:
1. Don’t forget to add “System.Data.SqlClient”, “System.Data.OleDb”, “System.Configuration” namespaces.
2. Earlier Version of Excel 2007 use below connection string:

Dim sSourceConstr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;"""

3. For Excel 2007 & Later Version use below connection string:

Dim sSourceConstr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath)

4. HDR=Yes indicates that the first row contains column names, not data. HDR=No indicates the opposite.
5. You can manually set the column mapping (if Excel column name differ with SQL Server Database Table column name) by the following way:

bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code")

6. Modify the Excel sheet name(Sheet1$) from the SQL as per your Excel sheet name.
7. Add a Folder Named “BulkFolder” in your root directory to keep the uploaded Excel File.

Now run the application & see the performence that how fast your application import all data into the database table.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, VB.Net
2 comments on “How to Import Excel Sheet Data into SQL Server Table using ASP.Net C# VB.Net
  1. Abdihakim Ahmed says:

    Thanks For Your Helping Me Thanks Alot

  2. Nalif Alhazmy says:

    Hi How is importing more than sheet data from Excel to sql server

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*