How to remove duplicate records from excel sheet using oledb

Question

I have an excel sheet with 4 column(JobCode,JobName,StartDate,EndDate). On the basis of one rule I have to validate the 1st excel sheet and insert all the record in 2nd excel sheet except the duplicate record which is present in 1st excel sheet. I tried to using list. But it's working as expected.

List<string> JobCodeList = new List<string>();
for (int iRowCount = 0; iRowCount < hrms_jobdata.Tables[0].Rows.Count; iRowCount++)
{
    JobCode = hrms_jobdata.Tables[0].Rows[iRowCount]["Job Code"].ToString();
    JobName = hrms_jobdata.Tables[0].Rows[iRowCount]["Job Name"].ToString();
    StartDate = hrms_jobdata.Tables[0].Rows[iRowCount]["Start Date"].ToString();
    EndDate = hrms_jobdata.Tables[0].Rows[iRowCount]["End Date"].ToString();
    JobCodeList.Add(JobCode + JobName);
}

connectionhrms_job.Close();


for (int iRowCount = 0; iRowCount < hrms_jobdata.Tables[0].Rows.Count; iRowCount++)
{
    JobCode = hrms_jobdata.Tables[0].Rows[iRowCount]["Job Code"].ToString();
    JobName = hrms_jobdata.Tables[0].Rows[iRowCount]["Job Name"].ToString();
    StartDate = hrms_jobdata.Tables[0].Rows[iRowCount]["Start Date"].ToString();
    EndDate = hrms_jobdata.Tables[0].Rows[iRowCount]["End Date"].ToString();

    DateTime convertedstart = DateTime.Parse(StartDate);
    StartDateFormated = convertedstart.ToString("dd-MM-yyyy");

    DateTime convertedend = DateTime.Parse(EndDate);
    EndDateFormated = convertedend.ToString("dd-MM-yyyy");

    List<string> dupvalue = removeDuplicates(JobCodeList);

    foreach (string value in dupvalue)
    {
        string jobcodename = value; 
    }

    string connectionStringdest = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathdestination + ";Extended Properties=Excel 12.0;";
    DbProviderFactory factorydest = DbProviderFactories.GetFactory("System.Data.OleDb");
    DbConnection connectiondest = factorydest.CreateConnection();
    connectiondest.ConnectionString = connectionStringdest;
    DbCommand command = connectiondest.CreateCommand();
    StringBuilder inserthrms_job = new StringBuilder();
    inserthrms_job = inserthrms_job.Append("Insert into [hrms_job$] values ('" + JobCode + "', '" + JobName + "', '" + StartDateFormated + "', '" + EndDateFormated + "','" + JobCode + " " + JobName + "') ");
    inserthrms_job = inserthrms_job.Append(";");
    command.CommandText = inserthrms_job.ToString();
    connectiondest.Open();
    command.ExecuteNonQuery();
    connectiondest.Close();
}

Ummm, part of your code formatting went wrong, and I don't see what your question is. You say what you want it to do, then you say list is working as expected... but not what trouble you are having. — Jesse McCulloch, Jan 11 '11 at 17:51
Hi, Need your urgent help. I have an excel sheet with 4 column(JobCode,JobName,StartDate,EndDate). On the basis of one rule I have to validate the 1st excel sheet and insert all the record in 2nd excel sheet except the duplicate record which is present in 1st excel sheet. — saurav2109, Jan 11 '11 at 17:57
How can I remove the duplicate records from an excel sheet using C# and insert all the records except that duplicate one in another excel sheet — saurav2109, Jan 11 '11 at 17:58
Is this a one time thing you are doing, or will it be something you will have to do often? — Jesse McCulloch, Jan 11 '11 at 18:09
Everytime i have to check the 1st excel sheet and validate against rule and then insert the same in 2nd excel sheet without duplicate records and I have to do it every time — saurav2109, Jan 11 '11 at 18:13

score 0 · Answer 1 · answered Jan 11 '11 at 20:29

0

When you query the source spreadsheet, just do a "Select top 1 field1, field2, field3 from [Sheet$] Group by field1, field2, field3". That way you only read the first record, not the duplicates.

answered Jan 11 '11 at 20:29

MAW74656

3,449
21
71
118

score 0 · Answer 2 · edited May 23 '17 at 12:26

Good question - I don't think it's possible via the SELECT statement in the oledbadapter per the one answer I see (if so, please advise).

See this OleDBAdapter Excel QA I posted via stack overflow.

Put your into DataSet into an object like I did at the bottom of the post.

Then extend your object via the enumerable.distinct (see MSDN example), so you your object returns distinct elements from a sequence by using the default equality comparer to compare values.

then at the bottom of that post:

var noduplicates = query.Distinct();
foreach (var rec in noduplicates)
    Console.WriteLine(rec.ManagedLocationID + " " + rec.PartID + " " + rec.Quantity);

How to remove duplicate records from excel sheet using oledb

2 Answers2

Linked