Truncate all values in a UIPath datatable

Help Studio
1

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

2

@bryan.davidson

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 ?

3

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

4

I would like to truncate all columns. There are 30 columns. Thank you

5

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.

6

Hi @bryan.davidson
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

Regards,
Nived N

7

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)

…etc

Of course, it would mean less code if you did do a For Each inside the For Each Row.

image
image708×443 13.3 KB

The expression on the left is CurrentRow.Item(columnName)

1 Like
8

Thank you - that is what I needed.

9

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).

10

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.

1 Like
11

Thank you for that reminder! I would not have caught that.

12

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