How to delete

How to remove duplicates while creating the excel sheets

Hi,

dt.DefaultView.ToTable(True,{“Col1”,“Col2”,“Col3”}) on a datable, passing in the array the collumn names you don’t want to have duplicate values.

Regards,

1 Like

Small correction, The column names you will pass are the one which will be part of the destination table.

dt.DefaultView.ToTable(True,{“Col1”,“Col2”,“Col3”}) if you pass all collumn will at the end should you the same table but removing duplicate row.

The process is called Distinct.

1 Like

How to overwrite old value based on one column name not all the columns

Hello,

Can you please be more specific, overwrite with what ? Can you give example such as Inital datatable and then how you would like it to look after the process?

Example :

Before :
 
Florent,3,5
Shruti,1,3
Florent,3,5
Shruti,1,5

After

Florent,3,5
Shruti,1,3
Shruti,1,5

Cheers.

before
emp_id name
123 abc
345 def
123 exd → this is the data which i want to add if there is same id present the old data should be overwritten by new one
after
emp_id name
123 exd
345 def

Are you able to use Invoke code activity ?

ya iam trying with invoke method iam not getting

I mean Invoke Code not method like under.

1 Like

Ok here is a solution.

1- Build two Datatable (Initial with your records), (Filtered, Empty one with column header with the first column set to unique)

2- Assign Primary Key of the filtered table with its first (in your case emp_id)
Done that way : filteredDt.PrimaryKey = New DataColumn() {filteredDt.Columns(0)}

3- Inside a For Each Row of the initial Table, check if the Filtered table contains the First collumn Value (emp_id).

Done that way: Assign ExistingRow = filteredDt.Rows.Find(row(0).tostring)

It will lookup for the value on the primary key column.

4- Then inside a if activity

If ExistingRow IsNot Nothing Then
Remove Existing Row from FilteredTable
Add row.ItemArray To FilteredTable (Array is require because the datarow still belong to Initial table)
Else
Add row.ItemArray To FilteredTable
End if

Finally, Only the most recent row should having primary key should remain.

An alternative approach would be replace the “If True Part” by : Assign existingRow(1) = Row(1).
This would just overwrite the value without deleting the row, Implying that you not necesserally have the freshest rows at the end.

Example Here.

FilterLastRecords.xaml (14.8 KB)

Cheers.

4 Likes

i don’t want to delete old data i want to append
before
emp_id name
123 abc
345 def
123 exd → this is the data which i want to add if there is same id present then append with old data
after
emp_id name
123 abc,exd
345 def

1 Like

Hello,

I would say simply Assign existingRow(1) = existingRow(1).toString & “,” & Row(1).toString() inside the true part using the second approach.

Cannot try this now, if any issue with it, let me know

1 Like

Different row or column ?

Please share the output you want once again.

Cheers.

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

if we use the assign activity as u given it has the following error

FilterLastRecords has thrown an exception

Message: This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row.

Source: Assign

Exception Type: RowNotInTableException

Are you using it in your own workflow or in the one that i shared ?

there would be prerequisites listed here if you would like to use it on your own.

1- Build two Datatable (Initial with your records), (Filtered, Empty one with column header with the first column set to unique)

2- Assign Primary Key of the filtered table with its first (in your case emp_id)
Done that way : filteredDt.PrimaryKey = New DataColumn() {filteredDt.Columns(0)}

3- Inside a For Each Row of the initial Table, check if the Filtered table contains the First collumn Value (emp_id).

Done that way: Assign ExistingRow = filteredDt.Rows.Find(row(0).tostring)

It will lookup for the value on the primary key column.

4- Then inside a if activity

If ExistingRow IsNot Nothing Then
Remove Existing Row from FilteredTable
Add row.ItemArray To FilteredTable (Array is require because the datarow still belong to Initial table)
Else
Add row.ItemArray To FilteredTable
End if

Can you shared the workflow you are working on maybe ?

2 Likes

i have fixed this issue

Anyway thank you for your response