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!!

Hey @Angelo_Savino :wave:

Could you please share a sample Excel for reference?

If you want to split the value in a single row then, you could use a temporary array and then use the split() function to split the dates.

If you want to format the respective single date in each column without loop then refer the below link:

@Angelo_Savino
In General the LINQ Syntax should/could look like following

(From row In Compressed2.AsEnumerable
Let dt= DateTime.ParseExact(row.Item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)
Select row).CopyToDataTable

  • AsEnumerable is used for bringing the rows into an enumerable sequence
  • There is no need to parseString into datetime (ParseExact Method) and then convert a datetime into a dateTime (Conver.toDate)

But this statement will not do any changes. the Let part stores the converted dateinfo as like a temporary variable, but will not not do any modifications. So the unedited row will be iterated (From In … is same like a for each iteration) and copy it to a datatable.

It looks like you tried to modifiy the datacolumn. This can be done in LINQ with converting and reconstructing the itemArray from row. The modified ItemArray can be used for adding a new row e.g. to an empty cloned datatable.

But updating column with a LINQ within an assign will not work.

Start to do it with a for each, check if the reconstruct the empty datatable approach will work for you. Last resort could be invoke code activity

1 Like

@Angelo_Savino - any reason why you’re againstusing a loop inside the datatable? Using LINQ is still going to iterate through the whole datatable, so why not just make it easy on yourself by doing the following:

Add data column - this will be where you put the new datetime column in your existing datatable. I’ll name this row “NewColumn” but you should name it whatever you want

For each row in Compressed2
Assign row.item(“NewColumn”) = DateTime.ParseExact(row.item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)
next row

Remove Data column - remove the old string column from the existing datatable

Super easy to understand what you’re doing and it can be done in 3-4 activities. It should be extremely fast as well, as it is just doing a single loop through your datatable

1 Like

Hi ,

Here is an activity to convert the data column to datetime in datatable.

Regards
Balamurugan.S

hello Balupad14

thanks for you contribute, I am use your provide activity package aways. it`s nice. I have problem now. when I useing Datetime ParseTo Datatbl then alert error:“DateTime ParseTo Datatable : String was not recognized as a valid DateTime”. Datetime formmat is “12/2/2021 3:27:11 AM”, type is string. can you help me?

I am find the problem. i choose C# type when create project , solved this problem before change type to VB

Hello Sir @balupad14 …I sincerely thank your efforts for creating a new set of activities. But I am not able to get the desired results.

I want to read the date of the form (YYYY-MM-DD) from one csv file and write it into an excel sheet in the same format. After I read the date through UiPath,when I try to write to excel, it is writing it in (MM-DD-YYYY) format
image

I used your “DateTime Parse To Datatable” activity. But I get the following error.
image

But you mentioned that it can also convert strings into the desired format.

Please check and let me know if I am missing out something.

Thank You.

1 Like

Hi @Pradyumna_TK ,

Is it possible to send me an example file ? It would be great to give a sample workflow to you.

Thank you
Balamurugan.S

list1.xlsx (10.0 KB)
New.xlsx (8.7 KB)

Hello sir, As .csv format is not allowed to share here, i converted the “list1” to excel and shared.
My aim is to read data from list1 file and write to “New” excel file in the same format.
YYYY-MM-DD. But the date in the “New” file is coming in the format MM/DD/YYYY.

Please check and help me out

Hi @Pradyumna_TK ,

Here is the output.

You did well. only change in the dateformat . check it here.

Sample here.

Pradyumna.zip (77.8 KB)

Regards
Balamurugan.S

Yes sir. Got it

Thank You So Much :smiley:

1 Like

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