Filter Data table having only date value

[RENU_KHALKHO]

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.

[ashwin.ashok ((TheCodingTheory))]

Hi @RENU_KHALKHO ,

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

Kind Regards,
Ashwin A.K

[Palaniyappan (Forum Leader)]

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

[RENU_KHALKHO]

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

[supermanPunch (Arpan)]

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

3. 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

[Palaniyappan (Forum Leader)]

@RENU_KHALKHO

12-02-2020

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

[ashwin.ashok ((TheCodingTheory))]

Hi @RENU_KHALKHO ,

Could you give this sequence a try?

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

[Palaniyappan (Forum Leader)]

Is it working now @RENU_KHALKHO

[RENU_KHALKHO]

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

[RENU_KHALKHO]

How we can manage if date holding time format like below

[ashwin.ashok ((TheCodingTheory))]

Hi @RENU_KHALKHO ,

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

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

[RENU_KHALKHO]

Hello Ashwin,

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

Below is the excel data

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

[ashwin.ashok ((TheCodingTheory))]

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

[RENU_KHALKHO]

Yes, It worked now,

Thank you so much