I just need to know the linquery for removing duplicate entries from the datatable
Here I have attached the sheet containing records.
TestingFiles.xlsx (8.8 KB)
and also attached the result sheet of the format required
ResultCheck.xlsx (8.7 KB)
Please help me with the solution containing the Linquery.
Hi @Kunal_Jain
Try this
dt = dt.AsEnumerable().GroupBy(Function(x) x.Field(Of String)(“Name”)).Select(Function(f) f.First).CopyToDaTatable
Cheers
Why do you want to use Linq instead of the very simple UiPath activity Remove Duplicate Rows?
2 Likes
Try this
Create two variables in the data table for reading your data and returning output.
Assign the output variable and in the set, value write the query statement
1 Like
Actually I need to distinguish based on name and date both.
If name and date both are duplicate than we need to remove one entry and if name is a duplicate and date is different than we have to keep both the entries
ResultCheck.xlsx (8.8 KB)
Here check the result sheet for the same.
Hi @Kunal_Jain
Try with this expression
(From d In DtBuild.AsEnumerable
Group d By k=d("Name").toString.Trim, k2=d("Date").toString.Trim Into grp = Group
Let ra = New Object(){grp.First()(0),k,k2}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable
Regards
Gokul
HI @Kunal_Jain
Update in the Expression based on Date Format
(From d In DtBuild.AsEnumerable
Group d By k=d("Name").toString.Trim, k2=CDate(d("Date").toString.Trim).ToString("dd-MM-yyyy") Into grp = Group
Let ra = New Object(){grp.First()(0),k,k2}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable
Regards
Gokul
Hi @Kunal_Jain
Please use this that should solve
(From d In dt.AsEnumerable
Group d By p1=d(“Name”).toString.Trim, p2=d(“Date”).toString.Trim Into g = Group
Let ra = New Object(){g.First()(0),p1,p2}
Select r = newdt.Rows.Add(ra)).CopyToDataTable
Cheers
Hii
I have more data to add in ra according to your code
How should I add more column data based on requirements.
Please find the input sheet below.
TestingFiles.xlsx (8.9 KB)
Based on the same code just let me know how to add more columns.
Hi @Kunal_Jain
You can try with expression
(From d In DtBuild.AsEnumerable
Group d By k=d("Name").toString.Trim, k2=CDate(d("Date").toString.Trim).ToString("dd-MM-yyyy") Into grp = Group
Let ra = New Object(){grp.First()(0),grp.First()(1),k,k2,grp.First()(4)}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable
Like this grp.First()(4),grp.First()(5),grp.First()(6) Here 4,5,6 is the index in the excel file
Regards
Gokul
Assign: Conversion from string “” to type ‘Date’ is not valid.
I am getting this error while running the code.
What to do?
Hi @Kunal_Jain
There is any empty row in the Date column?
What changes can we make in the same code if there are empty rows??
Because there can be empty rows for date column or name column also
HI @Kunal_Jain
Try with this expression
(From d In DtBuild.AsEnumerable
Group d By k=d("Name").toString.Trim, k2=d("Date").ToString.Trim Into grp = Group
Let ra = New Object(){grp.First()(0),grp.First()(1),k,k2,grp.First()(4)}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable
Regards
Gokul
If we are simply adding the above expression than the output has date with time stamp.
We want simple output as simple date
HI @Kunal_Jain
on teh output datatable use this outdt.AsEnumerable.Select(function(row) row(“Datecolumnname”).Tostring(“MM/dd/yyyy”)).CopyToDatatable
Use Date.ParseExact(row(“Datecolumn”).Tostring,“MM/dd/yyyy hh:mm:ss”,System.Globalization.Culturalinfo.InvariantCulture).Tostring(“MM/dd/yyyy”) if the column is not a date format already
can you just adjust the format in the above expression please
Anil_G
Yoichi
Akash_Javalekar1
jast1631
k.sakaguchi0804
singh_sumit
Nisha_K21
shaik.muktharvalli1
postwick
naoko0623