Condition to filter date from excel

I have a excel sheet in which I have filter a column based on date. If the column has date then I have to leave that column and need to move the next row. The column to be filtered has empty cells, dates, statements which is dynamic…

Hello @KKsekar

You can use Filter Datatable activity.

Thanks

could you plz give the value t be used

image

Hi @KKsekar,

First, get rid of blank lines with isEmplty. Can you share how you read the date format for other filters?

image

Regards,
MY

Can you share the screenshot of the excel which you are using

image

I need to process all the rows except if there is a date. i need to leave that row and proceed with the next

Date can be dynamic not the same date everytime

Is the format of the date be static or will it also change?

Format will be the same dd/MM/yyyy

can you try as below.

dt is the datatable after reading the excel.

dt.AsEnumerable().Where(Function (row) row(“ESTADO”).ToString.Equals(System.Text.RegularExpressions.Regex.Match(row(“ESTADO”).ToString, “^(?:(?:31(/|-|.)(?:0?[13578]|1[02]))\1|(?:(?:29|30)(/|-|.)(?:0?[13-9]|1[0-2])\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(/|-|.)0?2\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(/|-|.)(?:(?:0?[1-9])|(?:1[0-2]))\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$”)). CopyToDataTable

Am getting an error like source contains no datarows

Hey!

Check the modified expression:

dt.AsEnumerable().Where(Function (row) row(“ESTADO”).ToString.Trim.Equals(System.Text.RegularExpressions.Regex.Match(row(“ESTADO”).ToString, “^(?:(?:31(/|-|.)(?:0?[13578]|1[02]))\1|(?:(?:29|30)(/|-|.)(?:0?[13-9]|1[0-2])\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(/|-|.)0?2\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(/|-|.)(?:(?:0?[1-9])|(?:1[0-2]))\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$”)).CopyToDataTable

There might be a space in the column…

Try the above expression…

Regards,
NaNi

Still getting the same error.
do i need to write it in assign activity…?

image

image

@KKsekar

Can you please check the UpdatedDT… Is it having all the data in the excel sheet?

Heyy!

Try this:

Take one for each row

inside the for each row take one if condition

try like this

CurrentRow("ESTADO").ToString.Trim.Equals(System.Text.RegularExpressions.RegEx.Match(CurrentRow("ESTADO").ToString.Trim,"\d+.\d+.\d+")).ToString

Regards,
NaNi

yes its having the data from the read range

image

in the If condition am getting error like string to Boolean conversion

Hey,

Remove the last .ToString

Reference:

Regards,
NaNi

Its not recognizing the date…its showing as no date for all the rows bt there are dates in the column

Hey!

Could you please send me the xmal with excel…

If Confidential please DM me…

Regards,
NaNi