How to replace existing row of excel based on condition date is greater than existing User UiPath?
Same name, but different data which is created. Aug.04 is the latest data , so i want to replace the existing one
Example :
Output:
Hi,
How about the following? This sample makes group by Name then filter the latest one.
dt = dt.AsEnumerable.GroupBy(Function(r) r("Name").ToString).Select(Function(g) g.OrderBy(Function(r) CDate(r("Created Date"))).Last()).CopyToDataTable()
Sample
Sample20240808-2.zip (9.4 KB)
' Create a new DataTable to store the latest records
Dim latestRecords As DataTable = dt.Clone()
' Use LINQ to group by name and find the latest date
Dim groupedData = From row In dt.AsEnumerable()
Group row By Name = row.Field(Of String)("Name") Into Group
Select Group.OrderByDescending(Function(r) DateTime.Parse(r.Field(Of String)("Created Date"))).First()
' Add the latest records to the new DataTable
For Each row In groupedData
latestRecords.ImportRow(row)
Next
' Assign the filtered DataTable to the UiPath variable
dt = latestRecords
Output:
Hope this will help you
Hi @Jena
You can use the LINQ Expressions to get the required result, check the below steps for better understanding,
→ Use the Read range workbook activity to read the Excel and store in a datatable called DT.
→ Then use the Assign activity and write the below expression,
- Assign -> DT = DT.AsEnumerable.GroupBy(Function(row) row("Name").ToString).Select(Function(grp) grp.OrderByDescending(Function(X) DateTime.ParseExact(X("Created Date").ToString, {"MMM. d, yyyy","MMM. dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None)).First).Copytodatatable()
→ Then use the write range workbook activity to write the DT to the output Excel.
Check the below workflow for better understanding,
Sequence4.xaml (11.8 KB)
Check the below output Excel (Sheet1 has Input data and Sheet2 has Output Data),
expectedoutput.xlsx (10.5 KB)
Hope it helps!!
1 Like
Thank you for your reply ,
I see i have multiple types of Date formats , how can we handle different date formats.
Date formats samples :
May 28, 2024
June. 20, 2024
Apr. 07 2024
Thank you for your reply ,
I see I have multiple types of Date formats , how can we handle different date formats ? Is will work for all formats type ?
Date formats samples different types :
May 28, 2024
June. 20, 2024
Apr. 07 2024
Okay @Jena
I will change the expression based on the date formats you have check the below expression,
- Assign -> DT = DT.AsEnumerable.GroupBy(Function(row) row("Name").ToString).Select(Function(grp) grp.OrderByDescending(Function(X) DateTime.ParseExact(X("Created Date").ToString, {"MMM. d, yyyy","MMM. dd, yyyy","MMMM. dd, yyyy","MMMM. d, yyyy","MMMM dd, yyyy","MMM dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None)).First).Copytodatatable()
Hope it helps!!
' Assign the result to the DataTable
DT = DT.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of String)("Name")) _
.Select(Function(grp) grp.OrderByDescending(Function(row)
Dim dateStr As String = row.Field(Of String)("Created Date")
Dim dateValue As DateTime
' Try parsing the date using multiple formats
If DateTime.TryParseExact(dateStr, {"MMM. d, yyyy", "MMM. dd, yyyy", "MMMM. d, yyyy", "MMMM. dd, yyyy", "MMMM dd, yyyy", "MMM dd, yyyy","MMM.dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, dateValue) Then
Return dateValue
Else
Return DateTime.MinValue
End If
End Function).First()) _
.CopyToDataTable()
I hope this work for you if you have any other formats please put that formats in that Arrays
@rlgandu i tried with these date formats, i am getting the older entry instead of newer entry. I am suppose to get May 24, 2024 entry, instead i got Jul. 19, 2023 row entry
Jul. 19, 2023
May 24, 2024
Could you share the input excel file with us. If the data is confidential make a excel with dummy data and share with us… @Jena
Please change the datetime format as per your excel data,if single space is missing also it will give older date.So please give all your formats in that array,if you have new formats.
I tried adding multiple format , may be i am missing something ,here is my sample excel
Demo.xlsx (8.8 KB)
Please find the sample Excel for your reference
Demo.xlsx (8.8 KB)
' Assign the result to the DataTable
DT = DT.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of String)("Name")) _
.Select(Function(grp) grp.OrderByDescending(Function(row)
Dim dateStr As String = (row.Field(Of Object)("Created Date").ToString)
Dim dateValue As DateTime
' Try parsing the date using multiple formats
If DateTime.TryParseExact(dateStr, {"MMM. d, yyyy", "MMM. dd, yyyy", "MMMM. d, yyyy", "MMMM. dd, yyyy", "MMMM dd, yyyy", "MMM dd, yyyy","MMM.dd, yyyy","dd-MMMM-yy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, dateValue) Then
Return dateValue
Else
Return DateTime.MinValue
End If
End Function).First()) _
.CopyToDataTable()
input:
output:
@rlgandu This output is not correct as per my expectation
24-May-24 > Jul. 19, 2023
latest entry should replace the older entry
Expecting output like this :
Okay @Jena
Check the below LINQ Expression,
- Assign -> DT = DT.AsEnumerable().GroupBy(Function(x) x("Name").ToString()).Select(Function(grp) grp.OrderByDescending(Function(y) If(DateTime.TryParseExact(y("Created Date").ToString(), {"MMM. d, yyyy","MMM. dd, yyyy","MMMM. dd, yyyy","MMMM. d, yyyy","MMMM dd, yyyy","MMM dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, CDate(y("Created Date").ToString)), DateTime.ParseExact(y("Created Date").ToString, {"MMM. d, yyyy","MMM. dd, yyyy","MMMM. dd, yyyy","MMMM. d, yyyy","MMMM dd, yyyy","MMM dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None), CDate(y("Created Date").ToString))).First()).CopyToDataTable()
Check the below workflow for better understanding,
Output -
Hope it helps!!
How do i add a NULL check ?
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object. at UiPathCodeRunner_4a6a8f090ec743a18c9db1a9cf2df037._Closure$__._Lambda$__0-2(DataRow row)
at System.Linq.CachingComparer`2.SetElement(TElement element)
at System.Linq.OrderedEnumerable`1.TryGetFirst(Boolean& found)
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
at UiPathCodeRunner_4a6a8f090ec743a18c9db1a9cf2df037._Closure
@mkankatala How to add a NULL check, i have few entries which are having empty created Date field, we can add NULL check to ignore those. How to do that in LINQ ?
Or apply filter to ignore them
Example input :
This is throwing
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException: Conversion from string "" to type 'Date' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value)
at UiPathCodeRunner_55d87d9465aa4d53bbe859a576a203ba._Closure$__._Lambda$__0-2(DataRow y)
at System.Linq.CachingComparer`2.Compare(TElement element, Boolean cacheLower)
at System.Linq.OrderedEnumerable`1.TryGetFirst(Boolean& found)
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
at UiPathCodeRunner_55d87d9465aa4d53bbe859a576a203ba._Closure$__._Lambda$__0-1(IGrouping`2 grp)
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable`1 source, DataTable table, Nullable`1 options, FillErrorEventHandler errorHandler)
at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable`1 source)
at UiPathCodeRunner_55d87d9465aa4d53bbe859a576a203ba.Run(DataTable DT, DataTable& DtOut)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)
--- End of inner exception stack trace ---
at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)
at System.Reflection.MethodBaseInvoker.InvokeWithFewArgs(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args)
at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object[] args)
at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List`1 inArgs, IEnumerable`1 imps, Object[] args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
Okay @Jena
Change the LINQ Expression to below,
- Assign -> DT = DT.AsEnumerable.Where(Function(x) Not(String.IsNullOrEmpty(x("Created Date").ToString) orelse String.IsNullOrWhiteSpace(x("Created Date").ToString))).GroupBy(Function(x) x("Name").ToString()).Select(Function(grp) grp.OrderByDescending(Function(y) If(DateTime.TryParseExact(y("Created Date").ToString(), {"MMM. d, yyyy","MMM. dd, yyyy","MMMM. dd, yyyy","MMMM. d, yyyy","MMMM dd, yyyy","MMM dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, CDate(y("Created Date").ToString)), DateTime.ParseExact(y("Created Date").ToString, {"MMM. d, yyyy","MMM. dd, yyyy","MMMM. dd, yyyy","MMMM. d, yyyy","MMMM dd, yyyy","MMM dd, yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None), CDate(y("Created Date").ToString))).First()).CopyToDataTable()
It will work weather Created Date not contains any data.
Hope you understand!!