Asp.net: Import MS Excel sheet records into Sql server database

In this tutorial I am going to explain how to import the MS Excel sheet records into Sql server database in asp.net

Description:

In the previous article I have explained change the text color in Image dynamically and Different ways to disable auto-fill in browser for a textbox.

I have a MS excel file which have records/details of employees. I want to import all records from excel file into database table.

Implementation:

I have a table in Tb_Employee

HTML Markup:

<table>

        <tr><td>Upload Excel:</td><td><asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>

        <tr><td></td><td><asp:Button ID="btnupload" runat="server" Text="Upload"/></td></tr>

    </table>ssss

Add namespace

C#:

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Configuration;

using System.IO;

using System.Data;

VB:

Imports System.Data.OleDb

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.IO

Imports System.Data

C#:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

VB:

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

Validate the extension

C#:

protected bool IsValidExtension()

    {

        if (FileUpload1.HasFile)

        {

            string FileExt = Path.GetExtension(FileUpload1.FileName);

            FileExt = FileExt.ToLower();

            if (FileExt != ".xls" && FileExt != ".xlsx")

            {

                Response.Write("<script>alert('Upload Excel file only');</script>");

                return false;

            }

        }

        return true;

    }

VB:

Protected Function IsValidExtension() As Boolean

        If FileUpload1.HasFile Then

            Dim FileExt As String = Path.GetExtension(FileUpload1.FileName)

            FileExt = FileExt.ToLower()

            If FileExt <> ".xls" AndAlso FileExt <> ".xlsx" Then

                Response.Write("<script>alert('Upload Excel file only');</script>")

                Return False

            End If

        End If

        Return True

    End Function

Upload the Excel file

On upload button click write the below given code:

C#:

protected void btnupload_Click(object sender, EventArgs e)

    {

        try

        {

            if (IsValidExtension())

            {

                string filepath = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

                FileUpload1.SaveAs(filepath);

                OleDbConnection excelcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + filepath + ";Extended Properties=Excel 12.0;");

                excelcon.Open();

                DataTable ds = new DataTable();

                OleDbDataAdapter oda = new OleDbDataAdapter("SELECT *FROM [sheet1$]", excelcon);

                oda.Fill(ds);

                excelcon.Close();

                con.Open();

                SqlBulkCopy bulkcopy = new SqlBulkCopy(con);

                bulkcopy.DestinationTableName = "Tb_Employee";

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

                bulkcopy.ColumnMappings.Add("Phone", "Phone");

                bulkcopy.ColumnMappings.Add("Salary", "Salary");

                bulkcopy.WriteToServer(ds);

                con.Close();

            }

        }

            catch(Exception ex)

        { }

    }

VB:

Protected Sub btnupload_Click(sender As Object, e As EventArgs) Handles btnupload.Click

        Try

            If IsValidExtension() Then

                Dim filepath As String = Server.MapPath("~/upload/") + Path.GetFileName(FileUpload1.PostedFile.FileName)

                FileUpload1.SaveAs(filepath)

                Dim excelcon As New OleDbConnection((Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=") & filepath) + ";Extended Properties=Excel 12.0;")

                excelcon.Open()

                Dim ds As New DataTable()

                Dim oda As New OleDbDataAdapter("SELECT *FROM [sheet1$]", excelcon)

                oda.Fill(ds)

                excelcon.Close()

                con.Open()

                Dim bulkcopy As New SqlBulkCopy(con)

                bulkcopy.DestinationTableName = "Tb_Employee"

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

                bulkcopy.ColumnMappings.Add("Phone", "Phone")

                bulkcopy.ColumnMappings.Add("Salary", "Salary")

                bulkcopy.WriteToServer(ds)

                con.Close()

            End If

        Catch ex As Exception

        End Try

    End Sub

  

Build and run the application. Test it by uploading the excel file.

Result:

    In this article we have learn to how to import the MS Excel sheet records into Sql server database in asp.net using C# and VB.net. I hope you enjoyed this article.