Here I am going to show you the step by step process to import data from Excel to SQL Server.There are many cases where you need to import data from Excel file to your SQL Databse Table. This code to import data from Excel to SQL table does not contain any type of validations.
We have a sample Excel file Customer.xlsx of customer details from which we have to import data.
| Customer_ID | Customer_Name | Customer_Email | Customer_Mobile |
| 10001 | Nitin Kumar | nitin.kumar@acb.com | 9877889995 |
| 10002 | Sumit Garg | sumit.garg@acb.com | 4353536254 |
| 10003 | Shiv Sagar | shiv.sagar@acb.com | 7634347464 |
| 10004 | Raju Chaudhary | raju.chaudhary@acb.com | 8766542677 |
| 10005 | Manoj Kumar | manoj.kumar@acb.com | 7786365338 |
Step 1:
Crate a Sample table Customer_Master .
create table Customer_Master
(
Customer_ID int,
Customer_Name nvarchar(20),
Customer_Email nvarchar(50),
Customer_Mobile nvarchar(10)
)
Step 2:
Now create a page upload_customer.aspx.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<span style="color: Red">*</span>Select Excel File:
</td>
<td>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnupload" runat="server" Text="Upload" onclick="btnupload_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>Step 3:
Here is the code in customer_upload.aspx.cs.
using System;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Core;
using System.IO;
using System.Text;
using System.Data.OleDb;
public partial class customer_upload: System.Web.UI.Page
{
if (!Page.IsPostBack)
{
}
protected void btnupload_Click(object sender, EventArgs e)
{
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"~/Upload/" + hdnfield2.Value) + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
OleDbDataAdapter oda = new OleDbDataAdapter(ocmd);
ocmd.CommandTimeout = 0;
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
while(ode.Read())
{
string CustomerID = odr[0].ToString().Trim();string CustomerName = odr[1].ToString().Trim();
string CustomerEmail = odr[2].ToString().Trim();
string CustomerMobile = odr[3].ToString().Trim();
//Here using this method we are inserting the data into the database
insertdataintosql(CustomerID, CustomerName, CustomerEmail, CustomerMobile);
}
oconn.Close();
}
public void insertdataintosql(string CustomerID, string CustomerName, string CustomerEmail, string CustomerMobile)
{
//inserting data into the Sql Server
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into Customer_Master(Customer_ID,Customer_Name,Customer_Email,Customer_Mobile) values(@CustomerID,@CustomerName,@CustomerEmail,@CustomerMobile)";
cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = Convert.ToInt32(CustomerID);
cmd.Parameters.Add("@CustomerName", SqlDbType.NVarChar).Value = CustomerName;
cmd.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar).Value = CustomerEmail;
cmd.Parameters.Add("@CustomerMobile", SqlDbType.NVarChar).Value = CustomerMobile;
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
No comments:
Post a Comment