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”.
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.
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’.
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.
@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?
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…