Import Excel Data to SQL Server in ASP.NET

Today we will learn how to import data from an Excel sheet to SQL Server in ASP.NET. This is useful for many users who want to work with an Excel file and wants to save it or Export to the Database. So that it can be fetched anytime from there using a database.

In this article I will show how to export the data from Excel to a database in an
ASP.NET application. In my ASP.NET application a file is uploaded to a web server and then imports the file into SQL using the SQLBulkCopy Class.

Here are the steps to be followed.

Step 1

In this step will create an Excel file that contains some data.

Export-data-from-Excel-in-Asp.net.jpg

Step 2


To import an Excel file into SQL Server we need to create a table in the database. While creating the table you will make sure that the Scehma of the table is the same as the data in the Excel Sheet. Such as the same column names and data types of each domain.

Create-table-in-SQL-Server.jpg

Step 3

Now, we will create an ASP.NET application to import the Excel data to SQL Server.

Step 4

In this application I will use a FileUpload Control to upload the Excel file and a Button to import data from Excel to SQL Server.

Here is coding of the .aspx page:

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:FileUpload ID="FileUpload1" runat="server" />       

        <asp:Button ID="Button1" runat="server" Text="Export" OnClick="btnUpload_Click" />

    </div>

    </form>

</body>

</html>

Step 5

Here is the code of the .aspx.cs. file:

You need to include following Namespaces in the .cs file.

using System.Data.Common;

using System.Data.OleDb;

using System.Data.SqlClient;

First I saved the uploaded file to server application to the folder Uploaded File.

if (!FileUpload1.HasFile)

{

    string path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName));

    FileUpload1.SaveAs(path);

}

Create the connection string for the Excel file to read it's data. Here , we use an OleDbConnection and call ExecuteReader to read each row from the Excel file.

   // Connection String to Excel Workbook

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

OleDbConnection connection = new OleDbConnection();

connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

connection.Open();

  // Create DbDataReader to Data Worksheet

DbDataReader dr = command.ExecuteReader();


Then, I create other connection string for SQL Server database to export the data from the Excel sheet to be saved into the desired table.

Now, it's time to work with SQLBulkCopy to save the Excel data into SQL Server table. Here you need to give the destination table name where the data get stored in Database and at last call the WritetoServer method to inset the Excel data row into the table.

  // SQL Server Connection String

 string sqlConnectionString = @"Data Source=MYPC;Initial Catalog=Student;User ID=sa;Password=wintellect";

 

  // Bulk Copy to SQL Server

SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);

bulkInsert.DestinationTableName = "Student_Record";

bulkInsert.WriteToServer(dr);

Here is the full code of the .aspx.cs file:

using System;

using System.Collections.Generic;

using System.Data.Common;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

 

namespace ImportDataFromExcelToSQlServer

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void btnUpload_Click(object sender, EventArgs e)

        {         

            if (!FileUpload1.HasFile)

            {

                try

                {

                    string path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName)); 

                    FileUpload1.SaveAs(path);
 

                    // Connection String to Excel Workbook

                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

                    OleDbConnection connection = new OleDbConnection();

                    connection.ConnectionString = excelConnectionString;

                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

                    connection.Open();

                    // Create DbDataReader to Data Worksheet

                    DbDataReader dr = command.ExecuteReader();

 

                    // SQL Server Connection String

                    string sqlConnectionString = @"Data Source=MYPC;Initial Catalog=Student;User ID=sa;Password=wintellect";

 

                   // Bulk Copy to SQL Server 

                    SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);

                    bulkInsert.DestinationTableName = "Student_Record";

                    bulkInsert.WriteToServer(dr);

                    Label1.Text = "Ho Gaya";

                }

                catch(Exception ex)

                {

                    Label1.Text = ex.Message; 

                }

            }

        }

    }

}

Step 6

Now, build the application and run it to see the output.

Asp.net-application.jpg

Select the file using the FileUpload control and click on the Export Button.

You will see that the data of the Excel sheet is being imported into the specified table in the database.

inpot-data-in-sql-server-from-excel.jpg

Up Next
    Ebook Download
    View all
    Learn
    View all