Import Excel to SQL, we will learn how to import data from MS Excel sheet to SQL Database Table using ASP.NET C#. Microsoft Excel is popular spreadsheet application and widely used application.
You can read articles about Microsoft Excel or watch videos to learn Microsoft Excel here.
Background & Requirement
Why do we need to import excel to SQL?
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 large / bulk data entry
- if 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.
Software Used & Requirement
- Visual Studio
- SQL Server
- Microsoft Office
Creating GridView to Preview Data before Import
We have here implemented both methods. You can preview data in GridView & Import to SQL or you can directly import.
Code for above design is somehow like below.
<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. Upload & View button will also Save File in Server’s specified folder.
Important Rules/Requirements to Import Excel to SQL
Let’s make understanding clear. Below things are important and you should not miss them.
- Excel Sheet name must be exact same as programmed.
- Excel Column Data Type (content in column values) & SQL Column Data Type should be same.
- Excel Column Header Names (same as SQL Table Columns)
However, you can change these things using Backend programming. But in this article, we are just limited to above things.
Getting SQL Database Table Ready
Let’s create a Database table with following details.
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
);Microsoft Excel file structure
Here is the sample data in Excel sheet. Please remember, column names of above SQL Table & Excel Sheet heading are same.
Backend code (C#) for Upload & View Button
Let’s code for the buttons. First let’s learn include namespaces.
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;Description of need of above namespace usages.
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.
Code for Upload & View button.
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();
}
}
}In above code, we have done following tasks to import excel to SQL.
- imported Microsoft Excel file into Server, saved to Uploads folder.
- Validated its Extension to avoid possible error.
- Selected Connection String based on Excel version.
- Read content of Excel’s
Sheet1worksheet and stored in DataSet. - Selected DataSet table as GridView DataSource
- GridView DataBind
Output in GridView – Import Excel to SQL
Well, this is the result we were waiting. All the data that was in our MS Excel sheet is now imported & displayed into GridView. Let’s complete the last step and save data to SQL Database table.
Code for Import to SQL button (C#)
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 above lines of code, we imported file again, saved to server storage then using SqlBulkCopy class to import Excel’s Sheet1 data into Result table which is defined using DestinationTableName method of SqlBulkCopy class.
Final Output – Import Excel to SQL
As we see, we get successfully imported message & able to import excel to SQL table. Let’s summarize what we have done.
- Created a design with File Upload,
GridView& Two Buttons. One Button to Upload & Preview Data in GridView & other to Import to SQL - We prepared Excel File with same header name as SQL Database Table’s Column names & data type.
- We uploaded Excel file & saved in server, used
MapPathmethod & constructed ConnectionString to import into database or display in GridView.
Hope you find this article helpful. Do not forget to provide feedback. Till then, keep reading.
