How can I avoid duplicate rows while adding data to an Excel file?

[Mohammad_Rizwan_03 (Mohammad Rizwan)]

I have an Excel file with some data, and I don’t want to add any duplicate rows while adding data repeatedly.

[V_Roboto_V]

Hi @Mohammad_Rizwan_03

Are you using Workbook activity followed by LINQ or Are you using Excel Activities?

[Mohammad_Rizwan_03 (Mohammad Rizwan)]

Yes, I’m using excel activities

[Darshan_Sable]

@Mohammad_Rizwan_03

you can use remove duplicate rows activity to remove duplicate rows from data table orelse you can use LINQ query to remove duplicate rows

[V_Roboto_V]

Hi @Mohammad_Rizwan_03

Assuming you have 2 data tables (dt_Data, dt_NewValues). The dt_Data is from the excel, and dt_NewValues you are trying to add to the excel. You could use the Idea of Union of data tables using a simple LINQ, a faster approach.

Here is an example:
Data Table in Excel:

New Data Table to add:

Assign Statement:

dt_Data = dt_Data.AsEnumerable().Union(dt_NewValues.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

The OUTPUT:

If this solves your issue, Do mark it as a solution.
Happy Automation

[sharazkm32 (Sharaz K M)]

Please follow below steps to achieve the same

1. Read the Excel file into a data table - Named as dtExcel
2. Assuming the name of the source data table which is expected to add to excel as dtSource
3. Remove duplicates from dtSource using Remove Duplicate Activity if it is expected to have duplicates
4. Start looping through dtSOurce using For Each Row in Data table activity
5. The next step is to check whether the CurrentRow item is already available in Excel or not. For that use the below expression. For better performance - let’s use the Linq query in an if condition.

dtExcel.AsEnumerable().Any(Function(r) r(“Column1”).ToString().tolower().Contains(CurrentRow(“Column2”).ToString().ToLower()))

6. If value contains - Ignore that row. Else add to excel

For reference / testing - you can use below XAML
Main.xaml (14.4 KB)