Hello, I have empty cells in row 3. I want to use UiPath the value “0” inside the empty cells. Regardless any change in the data values (see pic 2), UiPath should be able to find the empty cells and write the value “0”
^pic 1
^pic 2
Assign activity:
dataTable = dataTable.AsEnumerable().Select(Function(row)
For Each col As DataColumn In dataTable.Columns
If row.IsNull(col) Then
row(col) = 0
End If
Next
Return row
End Function).CopyToDataTable()
- For Each Row in Excel
** For Each (column)
*** If currentRow(column).ToString = “” Then Write Cell 0
Hi @helpplease
=> Use read range workbook activity to read the excel and store in a datatable called excelData (Datatable datatype variable).
Use the below linq expression to do it quickly.
=> Assign activity -> rowIndex = 2
=> Assign -> excelData = excelData.AsEnumerable().Select(Function(row, index) If(index = rowIndex, row.ItemArray.Select(Function(cell) If(cell Is DBNull.Value OrElse String.IsNullOrWhiteSpace(cell.ToString()), "0", cell)).ToArray(), row.ItemArray)).CopyToDataTable()
=> Use write range workbook to write the same data in to the same excel.
Note - Assuming row 3 is at index 2 (0-based index)
Hope it helps!!
i used read range first to read that row and save the datatable as dt_emptyCells
then assign activity:
dt_emptyCells = dt_emptyCells.AsEnumerable().Select(Function(row)
For Each col As DataColumn In dt_emptyCells.Columns
If row.IsNull(col) Then
row(col) = 0
End If
Next
Return row
End Function).CopyToDataTable()
it did not work
rowIndex should be in Int datatype change the datatype.
In the value give 2 with out double quotes. @helpplease
Hope you understand!!
what do i change in the excelData “value to save”?
- Read Range (Excel / CSV) → Output: dataTable
- For Each row in dataTable.Rows
3. For Each column in row.ItemArray
4. If String.IsNullOrEmpty(column.ToString)
5. Assign: column = “0”
Then try this i hope it works
In the first assign you have created a rowIndex variable right change the datatype from string to int. In the value to save field give the 2 with out double quotes. @helpplease
yep, i changed datatype for the first assign but the second assign still have error
Use for each row in datatable
takt Another For each
Take for each row in datable
Inside take For each loop
Give me time i send you workflow
It’s my wrong I have made a workflow for this.
Check the below workflow
Expressions_Practice.xaml (13.3 KB)
I have checked it’s working.
Hope it helps!!
Follow the process steps for better understanding
=> Use read range workbook activity to read the excel and store in a datatable. Output - Datatable (Datatable Datatype)
=> Use for each activity to iterate the columns in datatable → Datatable.Columns
=> Inside for each Insert If condition to check the condition
- Condition -> String.IsNullOrWhiteSpace(Datatable.Rows(1)(currentItem.ColumnName).ToString())
=> In the block Insert the assign activity and give as below
- Assign -> Datatable.Rows(1)(currentItem.ColumnName) = "0"
=> Outside of loop use the write range workbook activity to write the datatable to same excel.
Note - Check the Add headers option for read range workbook and write range workbook activities.
Hope it helps!! @helpplease
Give me awhile, suddenly there’s another error above in my sequence
^ this means that there is empty cells?
I haven’t used the write cell activity anywhere. I have used write range workbook activity after the for each activity. Have you referred the workflow that I upload above.
If not refer the workflow and I have update the image also.
Hope you understand!!
it worked thank you so much
1 Like
sharazkm32
V_Roboto_V
Darshan_Sable
Bart_Simpson