Excel automation find the count

Help Studio
, ,
1

How can I count the number of characters in each cell in Excel, and set a condition so that if a cell contains more than 100 characters, it only displays the first 100 characters?

2

Hi @Sai17

Can you try below

The image depicts a sequence of activities in an automation workflow, including reading data from an Excel file, processing each row with a condition, and writing the updated data back to the Excel file. (Captioned by AI)
The image depicts a sequence of activities in an automation workflow, including reading data from an Excel file, processing each row with a condition, and writing the updated data back to the Excel file. (Captioned by AI)818×775 37.6 KB

CurrentRow("ColumnName") = If(CurrentRow("ColumnName").ToString.Length > 100, CurrentRow("ColumnName").ToString.Substring(0, 100), CurrentRow("ColumnName").ToString)

Regards,

3

@Sai17

If you want to check all columns, then try below

Method1: By using For Each loop

currentDataRow(currentDataColumn) = If(currentDataRow(currentDataColumn).ToString.Length > 100, currentDataRow(currentDataColumn).ToString.Substring(0, 100), currentDataRow(currentDataColumn).ToString)

This image shows a UiPath sequence workflow that reads data from an Excel file, processes each cell to handle nulls, and writes the updated data back to the same Excel file. (Captioned by AI)
This image shows a UiPath sequence workflow that reads data from an Excel file, processes each cell to handle nulls, and writes the updated data back to the same Excel file. (Captioned by AI)818×950 42.2 KB

Method2: By using Invoke Code activity

For Each row As DataRow In dt.Rows
    For Each column As DataColumn In dt.Columns
        row(column) = If(row(column).ToString.Length > 100, row(column).ToString.Substring(0, 100), row(column))
    Next
Next

The image displays a UiPath workflow consisting of three activities: reading a range from an Excel workbook, invoking code to process the data, and writing the processed range back to the Excel workbook. (Captioned by AI)
The image displays a UiPath workflow consisting of three activities: reading a range from an Excel workbook, invoking code to process the data, and writing the processed range back to the Excel workbook. (Captioned by AI)818×580 29.8 KB

The image shows a dialog box titled "Invoked code arguments" with a single argument named "dt" of type DataTable with In/Out direction and value "DT". (Captioned by AI)
The image shows a dialog box titled "Invoked code arguments" with a single argument named "dt" of type DataTable with In/Out direction and value "DT". (Captioned by AI)890×261 10 KB

Regards,

2 Likes
4

Thank you for the help. I’ll give it a try and let you know how it goes.

1 Like
5

Thanks, its working. I have small doubt can we use Linq on this scenario

6

Hi @Sai17 ,

You can achieve this with LINQ

Below is the Workflow Screenshot

image
image837×497 17 KB

and Below is the LINQ code used in an Assign activity

(From row In dt.AsEnumerable
Let updatedValues = dt.Columns.Cast(Of DataColumn).
                    Select(Function(col) If(row(col).ToString.Length > 100, 
                                                row(col).ToString.Substring(0, 100), 
                                                row(col))).ToArray
Select dt.Clone.Rows.Add(updatedValues)).CopyToDataTable

here dt → Source Datatable
dt_Output → is the Output datatable

Hope it helps you !

Best Regards,
Vikas M

1 Like