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]()