Remove duplicate rows in data table

[E.T.S]

Hello,

I am comparing two data tables and would like to remove duplicate rows - I am unsure how to do this. I attempted to use a filter data table activity but unsure how to configure the value part of the activity for a whole data table.

Is there an assign I can configure to remove the duplicating rows from the two data tables I am comparing?

Cheers

[vrdabberu (Varunraj Dabberu)]

Hi @E.T.S

dataTable = dataTable.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable()

Regards

[pikorpa (Piotr Kołakowski)]

Hey @E.T.S
you can try:

FilteredDataTable = DataTable1.AsEnumerable().Except(DataTable2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

[rlgandu (Rajyalakshmi Gandu)]

@E.T.S

Assign activity:
    combinedRows = dt1.AsEnumerable().Union(dt2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

[E.T.S]

Thanks everyone! The output using the above methods are a little skewed because my read range is not configured correctly. I would like the read range to read from cell A7 to L7 and continue reading the rows till there is an empty row - is there a way I can do this?

Cheers

[sarvesh.b (Sarvesh)]

Hi @E.T.S

First use read range activity for that sheet and get the row count of data filled in that sheet. Then write the next data by passing the rowcount variable in write range read range section.

Hope it works! Cheers

[E.T.S]

Thank you for your reply!

Please could you advise how I would get the row count of data filled

Thanks

[sarvesh.b (Sarvesh)]

First use read range activity with variable eg: “ReadDT”, then assign a variable suppose

rowcount=ReadDT.rows.count+1 (variable type Int)
In this assign output you will get the number of rows filled in excel sheet in number format.

Use another assign

RowCountplus=rowcount+1 (variable type Int)
In this you will get the next blank row after the last filled row in excel sheet

Then use write range activity to write the next data. so in range section enter Column no. from where you want to start eg: ‘A’+RowCountplus.tostring

Hope it will help you!

[E.T.S]

This seems to give me a incorrect result

[pikorpa (Piotr Kołakowski)]

@E.T.S
I see that you are using my solution. Can you mark solution option on my post?
Thank you

[sarvesh.b (Sarvesh)]

Use read range for the sheet on which you want to write the data and pass the same sheet name in write range activity.

In this you are counting the rows of different sheet and using write range for different sheet.

Thanks!