Filter Data table having only date value

Help Studio
1

Hi,

I have a data table, in one column I have mix data like it is having date , number and string value.

I want to filter the data that is having only date value in the column.

Could someone please tell me how I can achive this.

2

Hi @RENU_KHALKHO ,

Would you be so kind as to provide us with some sample data to work with?

Kind Regards,
Ashwin A.K

3

Hi

We can try with Datetime.TryParse

Once after getting the datatable as dt and if your datatavle has a standard Datetime format something like this dd/MM/yyyy then
use a assign activity like this

Dt = Dt.AsEnumerable().Where(Function(a) DateTime.TryParseExact(a.Field(of String)(“yourcolumnname“).ToString, “dd/MM/yyyy”, Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, Nothing)).CopyToDatatable()

You can mention your Datetime format you have in above expression

Cheers @RENU_KHALKHO

4

Hi Ashwin.

I have a datatable name dt1 having one column named DateValueCol holding different type of data like below

DateValueCol
123
STG
1gfe12
12-02-2020
655
12-08-2021

In the above column I want only date value i.e 12-02-2020 and 12-08-2021

5

Hi @RENU_KHALKHO ,

For a Quick Test Approach, we can do the Following :

  1. Let’s say you have the Datatable varaible, as DT.

  2. Then, We can Filter using Linq and Regex like the Below :

arrayRow = DT.AsEnumerable.Where(Function(x)System.Text.RegularExpressions.Regex.IsMatch(x("YourDateColumn").ToString,"\d{2}\/\d{2}\/\d{4}",RegexOptions.IgnoreCase)).ToArray

where arrayRow is a variable of Type Array of DataRow

  1. Next, We can Check, if there are rows in arrayRow using if Activity with Condition as below :
arrayRow.Any

In the Then Part, we use Assign Activity and Convert the arrayRow to Datatable

DT = arrayRow.CopyToDatatable

you could use a Different Datatable variable

1 Like
6

If this is the date format then try with this expression in a assign activity

Dt = Dt.AsEnumerable().Where(Function(a) DateTime.TryParseExact(a.Field(of String)(“yourcolumnname“).ToString, “dd-MM-yyyy”, Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, Nothing)).CopyToDatatable()

Cheers @RENU_KHALKHO

1 Like
7

Hi @RENU_KHALKHO ,

Could you give this sequence a try?
image

dt.AsEnumerable.Where(Function(w) DateTime.TryParseExact(w("DateValueCol").ToString.Trim,"dd-MM-yyyy",Nothing,Nothing,Nothing)).CopyToDataTable

OnlyDates.xaml (7.7 KB)

Kind Regards,
Ashwin A.K

8

Is it working now @RENU_KHALKHO

9

No, It is giving below error
Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’.

10

How we can manage if date holding time format like below

image
image809×517 17.3 KB

11

Hi @RENU_KHALKHO ,

Not a problem, you can simply change the date string format like so →

image
image714×368 13.4 KB

image

dt.AsEnumerable.Where(Function(w) DateTime.TryParseExact(w("DateValueCol").ToString.Trim,"dd-MM-yyyy hh:mm:ss",Nothing,Nothing,Nothing)).CopyToDataTable

Kind Regards,
Ashwin A.K

12

Hello Ashwin,

I am first reading the data from excel then filtering it , but it is showing below error

image
image1052×761 25.8 KB

Below is the excel data
image

In the assign
DtOut.AsEnumerable.Where(Function(w) DateTime.TryParseExact(w(“DueDate”).ToString.Trim,“MM/dd/yyyy”,Nothing,Nothing,Nothing)).CopyToDataTable

13

Hi @RENU_KHALKHO ,

Could you try with this format?

dd/MM/yyyy hh:mm:ss

If that doesn’t work, then try logging the date as string so that we can understand how the bot interprets it.

Kind Regards,
Ashwin A.K

1 Like
14

Yes, It worked now,

Thank you so much

15

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