I have an Excel spreadsheet imported to UiPath Studio as part of an information extraction from PDF process. This is stored in a data table.
I would like the robot to go through each record/row and each column, to check for missing cell values. If the cell value is missing, store the column header into an Array, and write line showing e.g., John’s DOB and Role is missing.
After which, the robot should send an email to e.g., John saying that his DOB and Role is missing, required to provide the information.
Use “Add Data Column” and set the column name to “Status” to your datatable.
Use a “For Each Row in DataTable” activity to iterate through each row in your DataTable
Initialize a List of Strings missingColumns = new List(Of String)
Use an If activity to check if the cell String.IsNullOrEmpty(CurrentRow("ColumnName").ToString))
or CurrentRow("ColumnName") is Nothing OrElse String.IsNullOrEmpty(row("ColumnName").ToString)
If a cell is found to be empty, add the column name to the list of missing columns. missingColumns.Add("ColumnName")
After checking all columns in a row, use an Assign activity to update the “Status” column of the current row based on whether any missing columns were found. CurrentRow("Status") = If(missingColumns.Count > 0, "Incomplete", "Complete")
If any missing columns are found for a row, use the any email activity to send an email notification.
Hi @pikorpa , i am facing an error as I have a for each row in data table activity, followed by a for each that loops through each column name (e.g, dt.Columns). When i use If String.IsNullOrEmpty(Candidate(Item.ToString).ToString
) and try assigning missingColumn to item.ToString where item refers to column name, there is an error saying Cannot assign from type ‘System.String’ to type 'System.Collections.Generic.List1[System.String] in Assign activity ‘Assign’.
I assume that “MissingInfo” is of type List(of String). In that case you don’t have to use an assign activity, you have to use the “Append Items to Collection” activity.
Could you maybe check with the below Expression in an Assign Activity:
MissingDataList = (From r In DetailsDT
Let MissingIndices = r.ItemArray.SelectMany(Function(x,j)If(String.IsNullOrWhiteSpace(x.ToString),{j},{}))
Where MissingIndices.Any
Select r("Name").ToString+"'s "+String.Join(", ",MissingIndices.Select(Function(x)DetailsDT.Columns(x).ColumnName))+" is Missing").ToArray
Here, DetailsDT is the datatable variable containing the input data from the Excel sheet read using Read Range activity and MissingDataList is a variable of type Array of String.
@UIPYH
Your variable ‘missingInfo’ should be List(Of String), so you need to add value to the list.
Also please use ‘append items to collection’ to add value to the list.
