Formatting time column in data table from AM / PM to HH:MM:SS

[Kevin_Legendre]

Hello,

Is there a way to format columns within a data table? I used the Read CSV activity to import data from a csv table into a data table in my automation and now I’m trying to format 4 time columns.

The time columns are currently in AM / PM format “1:06 PM” and I’m trying to format them within the data table to HH:MM:SS format “13:06:00”.

Any help would be appreciated, thank you!

[Srini84 (Srinivas)]

@Kevin_Legendre

Check below post for your reference

How to convert time string into 24 hrs format

@smita.mobifly buddy we need am or pm to get this in 24 hour format as 08:19 can be taken as both morning and night…but the compiler takes as morning So try like this we will get the value, else you were doing great Datetime.ParseExact(“16/05/2018 08:19 PM”,“dd/MM/yyyy hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“yyyy-MM-dd HH:mm:ss”) we will get answer like this 2018-05-16 20:19:00 Cheer buddy

Hope this will help you

Thanks

[Kevin_Legendre]

@Srini84

Thank you for the response, this is helpful. I’m trying to format the entire column so I used the assign activity. Within the assigned I called out the column I want to format but I’m receiving an error. I’ve included my assign activity along with the error below.

dtRatingsData.Columns(24) = Datetime.Parse(“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:SS”)

Error Message - Cannot assign from type ‘System.String’ to type ‘System.Data.DataColumn’ in Assign activity ‘Assign’.

[Srini84 (Srinivas)]

@Kevin_Legendre

Thank you for the response, this is helpful. I’m trying to format the entire column so I used the assign activity. Within the assigned I called out the column I want to format but I’m receiving an error. I’ve included my assign activity along with the error below.

@Kevin_Legendre

Use For Each row activity

Inside that assign activity as

dtRatingsData(24) = Datetime.ParseExact(row(24).ToString,“hh:mm tt ”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”)

Try as above

Hope this may help you

Thanks

[postwick (Paul)]

Datetimes, times etc do not have formats. They store values. You format them as you’re outputting them, with .ToString(format)

[postwick (Paul)]

You are trying to assign to the entire column, instead of a row AND column.

Columns(24) is a DataColumn object, not a “cell” that stores a value.

dtRatingsData.Rows(x).Columns(y) = Datetime.parse…

[Kevin_Legendre]

@Srini84

I used the for each row activity and I’m still getting the following error.

Error - Argument ‘Value’: Compiler error(s) encountered processing expression "DateTime.ParseExact(row(24).ToString,“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”). Value of type ‘String’ cannot be converted to System.Data.DataColumn’.

[Srini84 (Srinivas)]

@Kevin_Legendre

Instead of dtRatingsData.Column(24), write as dtRatingsData(24)

Hope this may help you

Thanks

[Kevin_Legendre]

@Srini84

With that tweak it is now showing the following error message.

Again I appreciate all the help!

[postwick (Paul)]

This is incorrect. It’s not an array. It’s a datatable, so you have to reference the row and column. dtRatingsData.Columns(24) is a datacolumn object, not the value in a row and column.

To get the value from a particular row and column, you have to do…

dtRatingsData.Rows(x).Item(z)

x = row index
z = column name or index

But since he is already looping through the rows, the row is represented by the “row” variable set up in the For Each Row. So the reference for a column would be row.Item(column name or index) and then you append the .ToString to get the value.

[Kevin_Legendre]

@postwick

Thanks for the correction. That got rid of my error but when I go to run my bot in debug mode it seems to be getting help up at my assign function.

I’m assuming it’s something within my time conversion function that is causing this hold up. See below.

DateTime.ParseExact(row.Item(24).ToString,“hh:mm tt”,new system.Globalization.CultureInfo(“en-US”)).ToString(“HH:mm:ss”)

[ppr (Peter Preuss)]

we can break it down to the building blocks:
for the conversion in general:

an implementation could look like this:
sample data:

Flow:

Result:

find starter help here:
ppr_DTUpdateBox_KevinLegendre.xaml (9.0 KB)

[Kevin_Legendre]

@ppr okay perfect that worked for me, appreciate the help!

[Kevin_Legendre]

@ppr One thing I’m finding when I run my bot it takes very long time for it to run through the for each row and assign activities. I’m only formatting four columns within my data set and my sample data set only contains about 40K rows. The first time I ran it took over a half hour. If I comment out this activity it only takes about a minute. Is it normal for this activity to take this long with this amount of rows? If so is there a more efficient solution?

[postwick (Paul)]

There is not a more efficient solution. More rows means more time. Keep in mind that if you’re using Debug mode, it runs more slowly.

[ppr (Peter Preuss)]

As mentioned debug mode is much slower as run mode. You can try parallelization e.g. with Parallel for each Activity (iterate over the datable: YourDataTable.AsEnumerable, set typeArgument to Datarow). Endure for this, that only the core task (update column values) is implemented and comment out any other unneeded activities e.g. log message…

Also have a readin to the topic here:

How to Update Data Column Values of a Data Table | Community Blog