how to find duplicate data while uploadind excel file

Hello,

i am creating a form to upload data from excel file to sql database.

but having an issue of saving data due to 1 field as unique, so i can't insert data it gives error.

can anyone tell me how to modify my code so i can append data to sql database using my web form and removing duplicates and giving notification how many removed kind ?/

please help me.

here is my frontend code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Data.Common;
public partial class uploadxml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile!=null)
{
try
{
string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));
FileUpload1.SaveAs(path);
// Connection String to Excel Workbook
string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
using (OleDbConnection con = new OleDbConnection(excelCS))
{
OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
con.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr = cmd.ExecuteReader();
// SQL Server Connection String
string CS = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);
bulkInsert.DestinationTableName = "Employee";
bulkInsert.WriteToServer(dr);
// BindGridview();
lblMessage.Text = "Your file uploaded successfully";
lblMessage.ForeColor = System.Drawing.Color.Green;
}
}
catch (Exception)
{
lblMessage.Text = "Your file not uploaded";
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}
}
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar7y ago
Hi Thenkraj,

Please try distinct in your query while fetching from excel sheet,
OleDbCommand cmd = new OleDbCommand("select distinct * from [Sheet1$]", con);
Gajendra Jangid7y ago
Hello dear Try these link https://www.codeproject.com/Questions/1028584/How-to-find-duplicate-data-in-the-EXCEL-File-in-cs https://www.c-sharpcorner.com/UploadFile/da14a2/new-microsoft-office-word-document/
Farhan Ahmed7y ago
https://www.c-sharpcorner.com/UploadFile/da14a2/new-microsoft-office-word-document/

https://forums.asp.net/t/1751358.aspx?Find+Duplicate+Records+In+Excel+While+Uploading+into+gridView+through+c+sharp