Sort datatable by column with date format "dd-mm-yyyy"

I am trying to sort (DESC) a datatable by a column, which comes in date format (dd-mm-yyyy); but the sort activity is taking it as a string. Any idea of how I might be able to accomplish this?

Many thanks for any input you might be able to provide with.

1 Like

Hi @mesalcan

You will have to parse it to date format first if you want to sort see this thread

Parse Date Activity

4 Likes

Hi @mesalcan

Just like @PrankurJoshi mentioned You need to parse the date to a date time format before you can sort. Since this is a datatable, you can follow the below steps too.

  • add a new column to your datatable which is of date time format to hold the converted date
  • use a for each row activity to loop through the datatable
  • within the loop, use an assign activity to convert the date. Snd assign it…

Hope it helps

2 Likes

Hello @mesalcan
you can also use linq here use this code in an Assign Activity and assign it to a Datatable Type Variable and write it in a sheet

Dt.AsEnumerable().OrderByDescending(Function(r) CDate(r("C2").ToString.Trim)).CopyToDataTable

Check this workflow for better understanding
Sorting Date.xaml (5.9 KB)
DateLQ.xlsx (8.6 KB)

4 Likes

HI,

I can see there is a compilation error in your xaml:

Thanks!
@vickydas

Hello @mesalcan

Try using the below component where it has an activity to sort the data table based on the date

Use the DataTable Column Sort activity here
https://go.uipath.com/component/datatable-plugins#

1 Like

Hi,

There is no input argument for column. My requirement is to sort a datatable based on specific column.

Thanks!

@Lahiru.Fernando

Good afternoon mesalcan,

Does the solution in this post resolve your issue?

Hi,

I am getting the error “String was not recognized as a valid DateTime”, even I have confirmed all column values are dates.

This is the statement I am using:

(From row In SalesReport Order By DateTime.ParseExact(row.Item(“Closing Date”).ToString, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable

and this is the full error message:

RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style) at lambda_method(Closure , DataRow ) at System.Linq.EnumerableSorter2.ComputeKeys(TElement[] elements, Int32 count) at System.Linq.EnumerableSorter1.Sort(TElement elements, Int32 count) at System.Linq.OrderedEnumerable1.<GetEnumerator>d__1.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable1 source, DataTable table, Nullable1 options, FillErrorEventHandler errorHandler) at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable1 source) at lambda_method(Closure , ActivityContext ) at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context) at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor) at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath) at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary`2 argumentValueOverrides, Location resultLocation, Int32 startIndex) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Thanks!

@chenderson

1 Like

you were almost done
kindly try with this expression
(From row In SalesReport Order By DateTime.ParseExact(row.Item(“Closing Date”).ToString.Substring(0,10), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable

if this doesnt work either if possible can i have a view on the date value in the excel may be a screenshot if possible

hope this would help you
Cheers @mesalcan

Hi,

I do not get the error now; but it seems it is not sorting it as expected.

Attached the data sampleTesting.xls (46.5 KB)

Thanks!

@Palaniyappan

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.