I need to make sure that all of the values in a particular datatable are no greater than 100 characters. I know I can perform a left(value,100), but I am having trouble figuring out how to loop through the datatable and update all the values. Thank you
Welcome back to our UiPath community.
Do you want to truncate all columns or only specific columns ? And also May I know how many columns input file has ?
Use For Each Row. Inside the loop, use an Assign to set row.Item(X) to left(row.Item(X).ToString,100)
X is the column index. Remember that the first column is index 0, the second column is index 1, etc.
Also, to reduce the number of updates, you could put the Assign inside an If that checks whether row.Item(X).ToString.Length is > 100
I would like to truncate all columns. There are 30 columns. Thank you
Thanks postwick. This worked for one column. But I can see now that I would need a loop within a loop to get all rows in all columns.
U can do like this as well using invoke code
reading the excel file and storing in dt1
now use the invoke code and make an In/Out argument dt1 where value should be dt1 datatable itself,
Then use the below code
dt1.AsEnumerable().ToList().ForEach(Sub(row) row.ItemArray= row.ItemArray.Select(Function(e) Left(e.ToString,100)).ToArray())
Then write the dt1 in excel
******* OR ***********************
Read the excel and store in dt1
Now use the below assign activity
dt2 = dt1.Clone
Now use a for each row activity
Inside it use the below assign activity
list_data= row.ItemArray.Select(Function(e) Left(e.ToString.Trim,100)).ToArray()
where list_data is String
Now add this list_data to dt2 using add data row
Now after the loop, use the write range to write the datatable dt2 to the excel
These two ways u can try out
No you wouldn’t. You could just have multiple assigns inside the For Each Row loop.
Assign row.Item(0) = Left(row.Item(0).ToString,100)
Assign row.Item(1) = Left(row.Item(1).ToString,100)
Of course, it would mean less code if you did do a For Each inside the For Each Row.
The expression on the left is CurrentRow.Item(columnName)
Thank you - that is what I needed.
Yeah it’s a little weird doing nested row/column loops on DTs. You’d think the columns array would be a property of the row, but it isn’t. It’s a property of the DT.
So you loop through the rows in the DT, then in the sub-loop you loop through the columns in the DT (not the columns in the row).
Oh, sorry, one more thing. When you drop in the For Each (sub loop) make sure you change its TypeArgument to String (it defaults to object). You want the loop to give you the column names as Strings (so CurrentRow.Item(columnName) will work) not the columns themselves as Objects.
Thank you for that reminder! I would not have caught that.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.