How to delete duplicate rows in an Excel file by using only Workbook activities

Help Studio
, , , , ,
1

I have situation where I have to delete duplicate information in an Excel file for two columns A and C (Member ID# and Last Name) using only Workbook activities. Excel application should not be used. Kindly help!

image

2

@Karunamurthy

dt1.AsEnumerable.GroupBy(Function(a) Tuple.Create(a(0).ToString,a(2).ToString)).Select(Function(grp) grp.First).copytodatatable

image
image980×404 7.82 KB

Hope this helps

3

Read the excel data and use the below LINQ

// Assuming 'dt' is your DataTable
dt = dt.AsEnumerable().GroupBy(Function(x) New With {
    Key .MemberID = x.Field(Of String)("Member ID#"),
    Key .LastName = x.Field(Of String)("Last Name")
}).Select(Function(g) g.First()).CopyToDataTable()

Or You can use Filter activity.

or you can follow below

4

Hi @copy_writes

I am getting the below error. Can you help?

image
image1392×237 18.2 KB

5

Change the type to double or in x.Field(Of String)(“Member ID#”)

I guess you are getting hear

// Assuming 'dt' is your DataTable
dt = dt.AsEnumerable().GroupBy(Function(x) New With {
    Key .MemberID = x.Field(Of Double)("Member ID#"),
    Key .LastName = x.Field(Of String)("Last Name")
}).Select(Function(g) g.First()).CopyToDataTable()
7

You can use default view.

dt_test.DefaultView.ToTable(true)
it makes your table distinct. See example

image
image940×501 32.5 KB