Transform datatable string column into data time format (DD-MM-YYYY)

Help Studio
1

Hello,

how do I transform columns that are of type String in my datatable into Date, and the column format is (DD-MM-YYYY) without using any loop

2

Hi @joao_adelio1,

Have you tried using CDate?

DateVar = CDate(StringVar)

As @postwick said, looping through the data is best if you are wanting to replace a value in each row of your datatable.

3

Why? Loops are good. Loops are how you do things like this. Everyone is so obsessed with avoiding loops. It’s unnecessary to avoid them.

Add a column with datatype datetime. For Each Row through the datatable and set the datetime column using Datetime.Parse to convert the string value into a datetime value.

4

can you give an example please?

5

because of the amount of data, when using loop it takes a very heavy workload, I’m talking about more than 300k rows.

6

For Each loops are very efficient. I had to get to like 5 million rows before any noticeable performance difference appeared. Even then it only took like 7 seconds to loop through all records. And the method proposed to avoid the loop was SLOWER.

https://forum.uipath.com/t/there-is-no-need-to-try-to-avoid-for-each-loops-on-large-amounts-of-data/

1 Like
7

I tested the same idea you are talking about. And I got a conclusion that when I am using simple assign activities, there is no HUGE difference in runtime between using LINQ and For Each.

8

Dear Joao,
You can use below to format it:
DateTime.ParseExact(row.item(“Date”).ToString,{“dd-MM-yyyy”,“d-MM-yyyy”},CultureInfo.InvariantCulture,DateTimeStyles.None)

9

Linq loops through the rows anyway. That’s what I think people don’t understand. ANY method of updating rows in a datatable MUST loop through all the rows.

2 Likes
11

that would be inside a loop, this?

12

Peter,

I really like your help on my topics with LINQ, do you have a solution for my problem shown above in the same way?

thanks in advance

13

Use Add Column to create a datetime column in the datatable.

For Each Row through the datatable and use Assign DateTime.ParseExact(row.item(“StringColumnName”).ToString,{“dd-MM-yyyy”,“d-MM-yyyy”},CultureInfo.InvariantCulture,DateTimeStyles.None) to the new datetime column.

Remove the string column if you want, using Filter Datatable (although not really necessary).

14

@joao_adelio1
when we do such a conversion we check in advance first following:

  • is the date string as expected within the datatable (we do know that mandatory what we do see in Excel / CSV is also within the datatable
  • is the present format consistent
  • do we also emmpty values to handle (it let fail the conversion)

Checking this we do on immediate panel:

  • set a breakpoint on relevant part within the flow
  • debug and get paused
  • open immediate panel (close to local tab)
  • type in: yourDataTableVar.Rows(0)(YourColNameOrIndex)

for checking all dates we ensure following:
grafik
use statement:

yourDataTableVar.AsEnumerable.Where(Function (x) Not DateTime.TryParseExact(x(YourColNameOrIndex).toString.Trim, "dd-MM-yyyy", CultureInfo.InvariantCulture,DateTimeStyles.None, Nothing)).toList

Maybe you can share the output with us.

Once this is cleared then would do the conversion within a for each row with following statement

Assign acitvity
left: row(YourColNameOrIndex2)
right:

DateTime.TryParseExact(row(YourColNameOrIndex).toString.Trim, "dd-MM-yyyy", CultureInfo.InvariantCulture)

YourColNameOrIndex2 will be an additional added column of datatype DateTime

So we let run this and will check how long this approach will take time for conversion

Once we do know this fact and conversion is not executed in an acceptable timeframe then we start to check on a few points for formal deriving factors for finding an alternate.

May we ask you to start as described. Thanks for support

15

Yes,it should be inside the loop. Would look for options if we can eliminate looping and perform the same

16

@geetishree.rao

dtData.Columns(2).Expression = “Convert(Substring(Column1,4,2) + ‘/’ + Substring(Column1,1,2) + ‘/’ + Substring(Column1,7,4),‘System.DateTime’)”

1 Like
17

Thanks

18

You can’t avoid looping. Any such update will loop, whether you are using a For Each or LINQ. Updating rows in a datatable requires looping no matter how you do it. For Each is plenty fast. Let go of the idea of avoiding it. For Each is fine.

19

Thanks Paul for the inputs…Actually the query was raised by Joao Paulo.He was interested for a solution for the same.

20

I’m getting the following error

image
image1506×635 43.5 KB

21

Please do following with us
Checking this we do on immediate panel:

  • set a breakpoint on relevant part within the flow
  • debug and get paused
  • open immediate panel (close to local tab)
  • type in: yourDataTableVar.Rows(0)(YourColNameOrIndex)