How to modify / change / update or replace value as date in data table cell using LINQ?

Hi,
I need to modify the date values in columns I and J.

In column I = Date1, I want to change the cell from the date format "MM/dd/yyyy HH:mm:ss" to the format "dd-MM-yyyy HH:mm:ss"

In column J = Date2 I want to change the cell from the date format "MM/dd/yyyy HH:mm:ss" to the format "dd-MM-yyyy"

I’d like to make changes without using a loop through the data table. I think the use of LINQ will be appropriate.
Can someone proficient in LINQ help me?

Sample data:

Place RMS Agency_Number Agency_Name ID ID_Name Policy Type Date1 Data2
WARSAW John Doe A00000001000 ABC A00000001001 Someone 920010010101 Policy 07/24/2020 21:33:34 07/24/2020 00:00:00
WARSAW John Doe A00000001000 ABC A00000001001 Someone 920010010101 Policy 07/24/2020 21:13:18 07/24/2020 00:00:00

@ppr

Hi @Adrian_Star - Could you please check this post…

You just have to tweak the date formats based on your requirements…

This uses Invoke code method, so there is no for each loop.

Hope this helps…

Hi @prasath17,
thanks for quick response.
How to handle error?

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.  ---> RemoteException wrapping System.ArgumentException: String was not recognized as a valid DateTime.Couldn't store <15-07-2020 14:12:12> in Date1 Column.  Expected type is DateTime.  ---> RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.

I have invoked code:

DT_Missings.AsEnumerable().ToList().ForEach(Sub(row) row("Date1")=DateTime.ParseExact(row("Date1").ToString,"MM/dd/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy HH:mm:ss"))
DT_Missings.AsEnumerable().ToList().ForEach(Sub(row) row("Date2")=DateTime.ParseExact(row("Date2").ToString,"MM/dd/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy"))
1 Like

Hi @Adrian_Star - Could you please share the sample excel sheet ? I will try something here and share the results…

The result comes directly from the SQL query and I’m working on System.DataTable (I don’t save it to a file). Probably the UiPath set the date format to Columns in DataTable?

This is what the data inside System.DataTable looks like:

In raw view of row dates are not a string:

{...,"Polisa", [07/15/2020 14:12:12], [07/15/2020 00:00:00] }

I will write data to the file only after modification.

@Adrian_Star
modify below xaml on the formats / column names and let run an anylisi job finding the non matching format dates:
FilterDates_NonValidDateFormat.xaml (8.0 KB)

@ppr
Works!
But Excel change my format in final file from:
image

to:
image

I think there may be such a date as well. The question of the date is open.

@Adrian_Star
It can happen that excel will reformat the values, regardless on how it was set within the datatable. But have a look on following activity and change the cell format after write range:
grafik

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