Import Excel Data into SQL Table using ASP.NET
Have you ever wondered how some applications provide you with features like uploading data into your Database tables. Lets learn here how to Upload Excel Data into SQL table using Asp.Net.
Time is most important and valuable in world. In the race of winning and saving time, sometimes we need to escape from Doing one by one Entries in our daily life, (office or school) if the data is same and repeating or the data is sensitive to Re-Enter etc.
To save the time of user, we can implement, Upload data into SQL Table feature in our application. Here we are going to Upload the whole Excel data in SQL Table as per need using ASP.NET.
What I will use:
Visual Studio 2012 Ultimate
Windows 8 Pro
SQL server 2008 R2
Microsoft Office 2010 Pro Plus
First lets clear our concept with design.
Now What is Required:
Excel Sheet Name (as same as programmed).
Excel Column Header Names (same as SQL Table Columns)
Data Type of Excel Columns (same as SQL Table)
However, you can change these things using Backend programming. But in this article, we are just limited to above things.
My SQL Table Design is as below:
CREATE TABLE [dbo].[Result](
[RollNo] [int] NULL,
[Name] [nvarchar](100) NULL,
[English] [int] NULL,
[Maths] [int] NULL,
[Social] [int] NULL,
[Economics] [int] NULL,
[Health] [int] NULL,
[Total] [int] NULL,
[Result] [nvarchar](50) NULL
);
And My Excel File design is as below:
Now here is the Code of above form Design.
<table width="60%" align="center" cellpadding="3" cellspacing="3">
<tr>
<td colspan="2" align="center">
<h1>Import from Excel to SQL</h1>
<p>
<asp:Label ID="lblStatus" runat="server"></asp:Label>
</p>
</td>
</tr>
<tr>
<td>Select File :
</td>
<td>
<asp:FileUpload ID="fileUpload1" runat="server" /></td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:GridView ID="gridView1" runat="server" EmptyDataText="This Gridview will show the Result of Selected Excel file." BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal" Width="409px">
<AlternatingRowStyle BackColor="#F7F7F7" />
<EmptyDataRowStyle BorderColor="Red" />
<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
<SortedAscendingCellStyle BackColor="#F4F4FD" />
<SortedAscendingHeaderStyle BackColor="#5A4C9D" />
<SortedDescendingCellStyle BackColor="#D8D8F0" />
<SortedDescendingHeaderStyle BackColor="#3E3277" />
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload & View" />
<asp:Button ID="Button2" runat="server" Text="Import to SQL" OnClick="Button2_Click" />
</td>
</tr>
</table>
We have placed 2 Buttons in above form. One is for Viewing Only and other is for Importing. View and Save button will Save File in Server's specified folder.Code at Backend
Following Namespaces are Required.
using System.Configuration;
// Accessing Connectiong String from web.config (Optional)
using System.Data;
// Required to Create Dataset.
using System.Data.SqlClient;
// Required to Create SQLConnection etc.
using System.Data.OleDb;
// Required to create OleDb connection and reader.
using System.IO;
// Uploading File in system. Path is class of this namespace.
Now see the Code for View Button Click Event.
protected void Button1_Click(object sender, EventArgs e)
{
if (fileUpload1.HasFile)
{
string fileName = Path.GetFileName(fileUpload1.PostedFile.FileName);
string fileExt = Path.GetExtension(fileUpload1.PostedFile.FileName);
string uploadPath = "~/Uploads/";
string filePath = Server.MapPath(uploadPath + fileName);
fileUpload1.SaveAs(filePath);
string conStr = "";
if (fileExt == ".xls" || fileExt == "XLS")
{
conStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + filePath + "'" + "; Extended Properties ='Excel 8.0;HDR=Yes'";
}
else if (fileExt == ".xlsx" || fileExt == "XLSX")
{
conStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties ='Excel 8.0;HDR=Yes'";
}
conStr = string.Format(conStr, filePath);
OleDbConnection con = new OleDbConnection(conStr);
try
{
con.Open();
OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]", con);
DataSet ds = new DataSet();
adp.Fill(ds);
gridView1.DataSource = ds.Tables[0].DefaultView;
gridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Dispose();
}
}
}
Lets Click on Button and See the Result.
Wow! It looks great.
Its time to save the Data in table. Code for Import Button Click Event.
protected void Button2_Click(object sender, EventArgs e)
{
string excelConnectionString = string.Empty;
string uploadPath = "~/Uploads/";
string filePath = Server.MapPath(uploadPath + fileUpload1.PostedFile.FileName);
string fileExt = Path.GetExtension(fileUpload1.PostedFile.FileName);
String strConnection = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
if (fileExt == ".xls" || fileExt == "XLS")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + filePath + "'" + "; Extended Properties ='Excel 8.0;HDR=Yes'";
}
else if (fileExt == ".xlsx" || fileExt == "XLSX")
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
}
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Result";
sqlBulk.WriteToServer(dReader);
lblStatus.Text = "Congratulations! Successfully Imported.";
excelConnection.Close();
}
In this Code, we have to Place file in FileUpload box and Click Import Button.
You can also download Source code of this Article with Excel file and Table Design.
All the Best.
John Bhatt