Asp.net: Import MS Excel sheet records into Sql server database - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

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:

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.netI hope you enjoyed this article.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon