How to eliminate duplicate rows in datatable with column index

Help
,
1

Hi,

I did a select range in an Excel file and I’ve recovered a datatable without column names.

The problem is I have duplicate rows in my datatable (I can’t eliminate them on Excel)

So, I need to know how to eliminate these duplicate rows using the column index on datatable (I’ve seen a post where the solution was using column names, but not the index)

Thanx

Nicolas

2

@nSangui

Just replace the column name with column index number

1 Like
3

It doesn’t works,

The method was DatatableName.DefaultView.ToTable(True, {“ColumnName1”,“ColumnName2”…})

If I change ColumName by a index i or “i”, I have the error “i is not a column name…”.

Have you other solutions to obtain a datatable without duplicate rows?

Thanks

4

There is another function called .Distinct you could look at.

Here is an example:
dataTable.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDatatable()

i just copied it from another post on here so you could search for more details. I believe it will return a datatable with only unique rows.

Thanks.

EDIT: I recommend using Datatable manipulation, but there’s also a good way to do it in the Excel Scope using key combinations.

So you can use Select Range to select entire table, then if you press Alt, A, M, a few tabs, space to deselect columns, then enter, it will use the Remove Duplicates button.

For example,
TypeInto
“[k(alt)]am[k(tab)][k(tab)][k(tab)][k(tab)][k(down)] [k(down)] [k(enter)]”

Like I said though, datatable manipulation is internal similar to VB and recommended.

5

Yes, I want to manipulate the datatable internally.

However I tried to do dataTable.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDatatable() and the rows are not removed…

The original datatable have 10 lines and when I try this code, it must have 4, without duplicates.

6

Columns always have names. By default Uipath names them Column0, Column1 and so on.

1 Like
7

Hi,

Additionally you can always determine the column name from its position as dataTable.Columns(index).ColumnName.

However, I also thought of a different solution for your problem:

  1. Clone the original datatable, e.g. dt2 = dt1.Clone, this gives you an empty table with the same column structure as dt1;
  2. Set the Unique property to True on relevant columns (or all) to enforce uniqueness, e.g. For Each column in dt2.Columns, column.Unique = True;
  3. Loop over the original table and add its rows to the clone with the Add Data Row activity: For Each row in dt1, Try Add Data Row, Catch ConstraintException. It is good practice to log any exception message, but you don’t need to do anything with it as it is just used to ignore the duplicates.
3 Likes
8

Thanks! That works perfectly for me!