如何使用c＃.net和asp.net从excel导入重复值 [英] How to import duplicate values from excel using c#.net and asp.net

问题描述

嗨EXperts，



我的门户网站有要求



我有3个类似EmpID Worklocation的颜色项目

其中工作位置是唯一的



如果您将重复记录插入到Excel中，它必须将所有重复记录保存到另一个表中，如错误表它将在另一个仅包含重复项的Excel中可用



注意：我需要在导出时验证excel中的重复记录，并且所有重复记录都应该保存在另一个表中，如ErrorTable



技术：C＃，ASP.NET，sql server2008





谢谢，

Siva



我的尝试：



直到uplodaing excel到db完成并且已经完成了下载。



现在我需要验证重复导出时来自excel的记录和所有重复记录应保存在另一个表中，如ErrorTable

请建议我使用代码

Hi EXperts,

I have requirement in my portal

I have3 coloumns like EmpID Worklocation Project
where Worklocation is Unique

if you insert duplicate records in to the excel it has to save all duplicate records in to another table like "Error table " it will be available in another Excel which contains duplicates only

Note:I need to validate the duplicate records from excel while exporting and all duplicate records should save in another table like "ErrorTable"

Technologies :C#, ASP.NET, ,sql server2008


Thanks,
Siva

What I have tried:

up to uplodaing excel to db is done and downloding as well done.

Now I need to validate the duplicate records from excel while exporting and all duplicate records should save in another table like "ErrorTable"
please suggest me with the code

推荐答案

如何从Excel导出到SQL，如果通过选择它只是基于它的问题关于你是怎么做的。



1）如果你在上传新数据之前删除了所有内容。



插入My_Table（EmpID，工作位置，项目）

选择DISTINCT EmpID，工作位置，项目

来自Excel_Book;



2）如果您在上传之前没有删除所有内容，那么您必须这样做。



I）更新旧表更改。 />


更新My_Table

设置工作位置= Excel_Book.Worklocation

项目= Excel_Book.Project

来自Excel_Book

WHERE My_Table.EmpID = Excel_Book.EmpId;



然后使用exists插入新记录：



插入My_Table（EmpID，工作位置，项目）

选择Distinct EmpID，工作位置，项目

来自Excel_Book AS

不存在的地方（选择EmpID，工作位置，项目

来自My_Table AS B

WHERE A.EmpID = B.EmpID和A.Worklocation = B.Worklocation和A.Project = B.Project）;



其他pocible解决方案只是插入后在triger上创建一个CTE过程



代码应该在triger里面这样的代码



与CTE（EmpID，Worklocation，Project，Dupplicatecnt）

AS

（

选择EmpID，工作位置，项目，ROWNUMBER（）超过（分区）按照EmpID，工作位置，项目，按照EmpID，工作位置，项目订购）AS Dupplicatecnt

来自My_Table

）

从CTE删除Dupplicatecnt> ; 1;



对于我过去所做的最后一件事，如果这是我在asp的门户网站上发布的，那么我现在所做的就是全部上传如果它更新并附加新的reords，或者在某些情况下，存储的prcedure只是为了删除所有新数据并进行清理，它将取决于您的要求，进入一个条目表和存储过程来执行必要的情况。

how are you exporting from Excel to SQL, if is throug select it is just matter to do it based on how you are doing it.

1) if you are deleting all before you upload the new data.

Insert into My_Table(EmpID, Worklocation, Project)
Select DISTINCT EmpID, Worklocation, Project
From Excel_Book;

2) If you are not deleting all before you upload then you must do it like this.

I) UPDATE OLD TABLE for changes.

UPDATE My_Table
Set Worklocation = Excel_Book.Worklocation
Project = Excel_Book.Project
FROM Excel_Book
WHERE My_Table.EmpID = Excel_Book.EmpId;

Then Insert the new records using exists:

Insert Into My_Table(EmpID, Worklocation, Project)
Select Distinct EmpID, Worklocation, Project
From Excel_Book AS A
Where NOT EXISTS(Select EmpID, Worklocation, Project
From My_Table AS B
WHERE A.EmpID = B.EmpID And A.Worklocation = B.Worklocation And A.Project = B.Project);

Other pocible solution is just create a CTE proces on a triger after inserting

the code should be like this inside the triger

WITH CTE(EmpID, Worklocation, Project, Dupplicatecnt)
AS
(
Select EmpID, Worklocation, Project, ROWNUMBER() OVER(PARTITION BY EmpID, Worklocation, Project, ORDER BY EmpID, Worklocation, Project)AS Dupplicatecnt
FROM My_Table
)
DELETE FROM CTE WHERE Dupplicatecnt > 1;

For Last what I have done in the past if this is comming from a web portal in asp what I have done and actually I currently do is upload all in to an entry table and a stored procedure to do the necesary case if it like update and append new reords or in some cases the stored prcedure is just to delete all before uplad the new data and do a clean up it will depends on your requirement.

我有excel表，

我们将数据从excel上传到sql server

如果在上传时发现任何重复记录

重复记录应存储在另一个表中，如ErrorTable使用c＃.net和sql server2008





这就是我的任务

Hi,
I have excel sheet,
we are uploading the data from excel to sql server
if it finds any duplicate records at the time of uploading
the duplicate records should store in another table like "ErrorTable" using c#.net and sql server2008


thats my task

IExcelDataReader excelReader = null;
DataSet result = null;
FileStream stream = null;
stream = File.Open(Server.MapPath("FilePath.xls"), FileMode.Open, FileAccess.Read);
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
result = excelReader.AsDataSet();
stream.Close();
stream.Dispose();
excelReader.Close();
result.Dispose();

这篇关于如何使用c＃.net和asp.net从excel导入重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！