如何使用oledb从excel表中删除重复的记录 [英] How to remove duplicate records from excel sheet using oledb

问题描述

需要您的紧急帮助。
我有一个excel表与4列（JobCode，JobName，StartDate，EndDate）。在一个规则的基础上，我必须验证第一个excel表，并插入第二个excel表中的所有记录，除了第一个excel表中存在的重复记录。
我试图使用列表。但它的工作如预期。请帮助

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. I tried to using list. But its working as expected. Please help

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();
}

推荐答案

t认为可以通过oledbadapter中的SELECT语句根据我看到的一个答案（如果是，请指教）。

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).

看到这个 OleDBAdapter Excel QA 我通过堆栈溢出发布。

See this OleDBAdapter Excel QA I posted via stack overflow.

将DataSet放入一个像我在帖子底部的对象。

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

然后通过enumerable.distinct扩展你的对象（请参阅MSDN示例），因此您的对象通过使用默认的等效比较器比较值来从序列中返回不同的元素。

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.

然后在该帖子的底部：

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

这篇关于如何使用oledb从excel表中删除重复的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！