Import data from EXCEL to SQL DB in ASP.NET

Post date: May 27, 2013 3:51:37 AM

In this post I will explain you how to import data from excel to database in .Net (C#). The code shown below will work for all excel formats and you will be easily able to use without any issues

Lets get into the coding

First create an excel file as shown in the figure below

Now we want to copy above excel data into SQL Database using .Net. Lets assume we have a table in SQL DB called EmpData with same scheme as shown below

Now its time to get into .Net. Create the new aspx page in your .net project/application and paste the below code

</head>

<body>

<div>

<table>

<tr>

<td>

<span style="color: Red">*</span>Attach the Excel File

</td>

<td>

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

</td>

</tr>

<tr>

<td>

</td>

<td>

<asp:Button ID="SendButton" runat="server" Text="Export" OnClick="SendButton_Click" />

</td>

</tr>

</table>

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

</div>

</form>

</body>

</html>

Once the aspx page is done, move to codebehind page and copy the below code for button click event

protected void SendButton_Click(object sender, EventArgs e)

{

String strConnection = "Data Source=MySystem;Initial Catalog=MySamplesDB;Integrated Security=True";

//Get File Upload Path

string path = FileUploadExcel.PostedFile.FileName;

//Create the Connection string

string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

//Create the Connection to Excel

OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel

OleDbCommand cmd = new OleDbCommand("Select [EmpId],[EmpName],[EmpDesignation] from [Sheet1$]", excelConnection);

excelConnection.Open();

OleDbDataReader dReader;

dReader = cmd.ExecuteReader();

//Use SQLBulkCopy to copy the excel data into SQL DB

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

//Give your Destination table name

sqlBulk.DestinationTableName = "EmpData";

sqlBulk.WriteToServer(dReader);

excelConnection.Close();

}

At the end don't forget to close the connection to excel. The select query is from sheet 1 of excel hence the select query contains Sheet1$.

Don't forget to add below two lines in codebehind page

using System.Data.OleDb;

using System.Data.SqlClient;

We can also create a aspx page to dynamically select which sheet to use/read data from and which row (will be available in next post)