Datatable : removing blanks from particular column and removing dates other than current date

[shrayud (Shrayus)]

Hi,

I have a data table in the below format:

SlNo,TRANSACTION_ID,PNR_NO,OPERATOR_ID(Booking),Operator_ID(Cancellation),PG NAME,CLASS,REFUND_AMOUNT,WAITING_AUTO_CANCELLED,TRANSACTION_DATE,TDR_CAN,USER_ID,PRINCIPAL_USER_ID,CANCELLATION_ID

I need to delete all rows where Operator_ID(Cancellation) is blank. I need to delete all rows where TRANSACTION_DATE is not yesterday.

Kindly suggest how to do this using LINQ.

[Pravin_Patil1 (Pravin Patil)]

@shrayud

Use filter data table activity

[shrayud (Shrayus)]

HI Pravin,
Thanks but Date is in time stamp format :

3/15/2021 6:58
3/14/2021 16:29
3/13/2021 9:16

I will keep 3/15/2021 6:58 and delete the rest

[Pravin_Patil1 (Pravin Patil)]

Yes…You can do that

[shrayud (Shrayus)]

Hi Pravin,

This is not working in case of dates.

I am getting output dataTable as blank.

[Yameso (Jakub Swiderski)]

First check if column TRANSACTION_DATE in your dt has DateTime value type. If not u can parse it like here:

Convert datatable strings column to datetime

Hi everyone! i need to convert a column of string(such as “25/08/20”,“15/08/20”,“17/07/20”) from a datatable to datetime formats without loop inside datatable. i have a problem with this assign: (From row In Compressed2 let dt= convert.ToDateTime(DateTime.ParseExact(row.Item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)) Select row).CopyToDataTable nothing happens with this expression… Help Me Please!!

Next use filter with remove for Operator_ID(Cancellation) is empty.

Use another filter activity with keep for:

TRANSACTION_DATE >= Today.AddDays(-1) AND
TRANSACTION_DATE < Today

[Pravin_Patil1 (Pravin Patil)]

@shrayud
Replace cancel_date filer with >= Now
or cancel_date <= now.adddays(-2)

[ppr (Peter | MVP)]

@shrayud

we can use a LINQ statement. It allows us a good control on datatype and conversion handlings

Use an assign activity
Leftside: dtFiltered - Datatype: datatable
Right side:

(From d In YourDataTableVar.AsEnumerable
Where Not (IsNothing( d("Operator_ID(Cancellation)")) OrElse String.IsNullOrEmpty(d("Operator_ID(Cancellation)").toString.Trim))
Where Not CDate(d("TRANSACTION_DATE").toString.Trim).Date <> now.AddDays(-1).Date
Select d).CopyToDatatable

For a defensive handling of empty result we can apply following pattern:

[Yameso (Jakub Swiderski)]

I assume I can change that cdate to datetime.parseexact to show the pattern of the string if I know it? I would use that in my automation

[ppr (Peter | MVP)]

yes. This can be done and it gives a more deep level of control we often do this as well