Filter data table after date & time

Help
1

Hi,

I am having a data table extracted from Excel that contains a column with Dates.

The current values looks like:

image

How can the data be filtered after the date and then by the time, so that the order is completely opposite? Or if possible, just extracting the data and inserting them into the data table in the opposite order?

2 Likes
2

Kindly have a viw on this thread pls

Cheers @Karsten_Bertelsen

3

Good morning Karsten,

Please see below for an additional solution to your request:

4

Hi @Karsten_Bertelsen

First sort the date by the following query

DataView dv = Dt.DefaultView;
dv.Sort = “Date asc”
DataTable sortedDT = dv.ToTable()

then Dt.Select(“[Date]<>‘Now.ToString.AddDays(-1).ToString(“dd.MM.yyyy hh:mm:ss”)’ and [Date]<>Now.ToString.ToString(“dd.MM.yyyyhh:mm:ss”)'”).CopyToDatatable()

Check this

Thanks
Ashwin S

5

Do anyone have experience with filtering after date and time?

I guess that if I can make UiPath recognize the column “Date” in my data table as first a date, and then time (something like: "dd.MM.yyy hh.mm), then it would work?

But how do i replace the existing value “03.09.2019 01:01”, so that it is not a string, but a date & time?

6

@Karsten_Bertelsen
Filtering on a date can be done with:
e.g. LINQ where statement
Parsing dateTime and e.g checking on String level or with second parsed compair info

just provide us a row with sample dataand the filter criteria, so we can help you on statement developing

DateTimeParsing (e.g. for UK Locals):
YourDateTimeVariable = DateTime.ParseExact( YourDateStringVariable , “YourFormatString”, new CultureInfo(“en-GB”))

DateTime to String
YourDateTimeVariable.toString(“YourFormatString”)

1 Like
7

Hi ppr,

Here is a small sample of the data.

Sample data.xlsx (9.0 KB)

The data should be filtered with the newest date and time in the top, and chronologically get older in date and time for every line downwards.

Thanks for the respond

8

Can be tried with LINQ, Keep in Mind that your data has some offset in the table, maybe you delete some rows before.
Statement: YourDataTableVar.AsEnumerable.OrderBy(Function ( r ) DateTime.ParseExact( r(0).toString , “dd.MM.yyyy HH:mm”, new CultureInfo(“en-GB”)).CopyToDataTable

feel free to change the cultureinfo to your needs.

9

Should this be done in an “assign” activity inside a “for each row” activity?

10

no for each is needed, can be done with assign activity and statement returns a datatable. But cleanup/delete before the unused rows from beginning

11

Ahh okay, here is the cleaned up data

Sample data.xlsx (8.9 KB)

12

@Karsten_Bertelsen
Please update formatstring to dd.MM.yyyy HH:mm

Readin Excel
delete some rows
using assign activity with statement from above

similiar to:
Sequence.xaml (7.8 KB)

Can you complete it by yourself?

13

Hi again,

Thanks for trying to help me, but I can not make it work :confused:

It works perfectly with your example, but even when I am copying your assign activity into my own sheet (and changes the variable name), it comes up with an error.

image
image371×611 11.7 KB

I appreciate your help!

14

Please help me to understand your intention of what you want to achieve.
Based on my snippets filtering was working
For what reason do you use Invoke Method ImportRow?

15

I am opening the latest downloaded Excel file with the Excel Application Scope

Then I am reading the Excel sheet with Read Range

Then I Build Data Table

I then use For Each Row and the Invoke Method to import each of the rows from the Excel file into the Data Table.

Then comes the step where I need the have the Data Table filtered after the date and time.

16

This confuses me. Read Range has as an output a datatable. so the Excel Data is already there. You can use this for the order statement

17

Okay, I might have mixed it up then. But it works.

I just tried to copy everything into your uploaded, and together with your assign activity, it works!

I just have no clue why it did not work in my own sheet.

Thanks for the help @ppr! :+1:

18

Ok great when its working, Feel free to flag the post that was you helping as solution, so others can benefit from

19

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