Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to import data from Excel sheet to SQL Server database table in ASP.NET

Author: Mike Green
Download Source Code : 1378_ExcelToSQLDatabase.zip

I will explain in this article that how we can import data from excel sheet to SQL Server database table in ASP.NET.

 

 

There are other ways to write excel sheet data to SQL Server database but the easy and fast way is to use SqlBulkCopy class. It provides performance and speed over other methods to transfer data from excel to SQL Server database. SqlBulkCopy class has WriteToServer() method which can take DataTable or IDataReader parameter to write data to destination table of SQL Server database.
 
Most important to read data from excel sheet is to set connection string in a correct format. You can provide your connection strings in web.config file or in code file but you must have to follow correct format. Other thing to keep correct is the column mapping between excel sheet and SQL Server database table. SQL Server database table should have same number of columns that you want to import from excel and insert into table. I have created a database “Employees” and a table “Employees” to write data from excel file “Employees.xlsx”.
 
  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add.
  5. Write code below in default.aspx page

    <asp:Button ID="Button1" runat="server" Text="Import from Excel to SQL Server"
                onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>
     
  6. Include following namespaces in your code file

    C#
     
    using System.Data.OleDb;
    using System.Data.SqlClient;
     
    VB.NET

    Imports System.Data.OleDb
    Imports System.Data.SqlClient
     
  7. Write below code in button click event to read data from excel sheet and copy into SQL server database

    C#
     
    protected void Button1_Click(object sender, EventArgs e)
    {
        string connStringExcel = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Employees.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
        OleDbConnection excelConn = new OleDbConnection(connStringExcel);
        OleDbCommand excelCmd = new OleDbCommand("Select * From [Sheet1$]", excelConn);
     
        try
        {
            excelConn.Open();
            OleDbDataReader excelReader = excelCmd.ExecuteReader();
     
            string connStringSql = "Data Source=(Local);Initial Catalog=Employees;Integrated Security=True";
            SqlConnection sqlConn = new SqlConnection(connStringSql);
            try
            {
                sqlConn.Open();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
                bulkCopy.DestinationTableName = "Employees";
                bulkCopy.WriteToServer(excelReader);
                Label1.Text = "Data successfully copied to SQL Server database table";
            }
            catch (Exception exs)
            {
                Label1.Text = exs.Message;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        catch (Exception exo)
        {
            Label1.Text = exo.Message;
        }
        finally
        {
            excelConn.Close();
        }
    }
     
    VB.NET
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim connStringExcel As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Employees.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""
        Dim excelConn As New OleDbConnection(connStringExcel)
        Dim excelCmd As New OleDbCommand("Select * From [Sheet1$]", excelConn)
     
        Try
            excelConn.Open()
            Dim excelReader As OleDbDataReader = excelCmd.ExecuteReader()
     
            Dim connStringSql As String = "Data Source=(Local);Initial Catalog=Employees;Integrated Security=True"
            Dim sqlConn As New SqlConnection(connStringSql)
            Try
                sqlConn.Open()
                Dim bulkCopy As New SqlBulkCopy(sqlConn)
                bulkCopy.DestinationTableName = "Employees"
                bulkCopy.WriteToServer(excelReader)
                Label1.Text = "Data successfully copied to SQL Server database table"
            Catch exs As Exception
                Label1.Text = exs.Message
            Finally
                sqlConn.Close()
            End Try
        Catch exo As Exception
            Label1.Text = exo.Message
        Finally
            excelConn.Close()
        End Try
    End Sub
     
    First we have to provide connection string. If we are using Excel 97-2003 file then we have to give Provider=Microsoft.Jet.OLEDB.4.0 and on the other hand if we are using excel 2007 file then we have to give Provider=Microsoft.ACE.OLEDB.12.0. Extended Properties “should be in a special format. For excel 2007, It should be written as Extended Properties=““Excel 12.0;HDR=Yes;””.
    We have created OledbConnection object and set connection. Then we have created OledbCommand object by providing query to get data from Excel Sheet1 and connection. We have created OledbDataReader object and called the ExecuteReader() method to read data from Excel sheet.
    We have written connection string for SQL Server database and set our connection. Then we have created SqlBulkCopy object and set the DestinationTableName property as “Employees”. At the end we have to call the WriteToServer() method of SqlBulkCopy class by providing OledbDataReader object. 
     
  8. Now you can browse the web site and import data from excel to SQL Server table.
 

 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to get data from multiple tables using stored procedure with multiple select statements in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net