Replace blank value in datable with NA

I want to replace a blank value in datable with the text NA

Could u plz tell any other method i m not familiar with linq

Hello @Neha_Aggarwal1

You can take an ‘Invoke code’ activity and use the following code:

Dim dt As DataTable = [Your DataTable]
For Each row As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
If row(col).ToString() = " " Then
row(col) = “NA”
End If
Next
Next

HI @Neha_Aggarwal1

Checkout this

Regards
Sudharsan

use for each row activity and update the datacolumn e.g with assign activity

  • row(“YourColNameOrIndex”) =
If(isNothing(row("YourColNameOrIndex")) OrElse String.IsNullOrEmpty(row("YourColNameOrIndex").ToString.Trim, "NA",row("YourColNameOrIndex").ToString )

When all data columns are to handle we would do

For each row Acitvity | row in YourDataTableVar

  • For each activity | item in row.ItemArray
    • AssIgn Activity:
      If(isNothing(item) OrElse String.IsNullOrEmpty(item).ToString.Trim, “NA”,item.ToString )

@ppr could u plz tell how to fix it

show us a screenshot from your modelling or share your XAML. Thanks

@Neha_Aggarwal1

row(“Comment”).ToString

Use .ToString in all the place where you are using Row()

Regards
Sudharsan

But not within isNothing(…) methods

Hello @Neha_Aggarwal1 , I’ve designed a solution which updates all the black spaces in the datatable regardless of number of rows or columns Without using LINQ. Please find the attached file below. Hope this helps :slight_smile:

Input:
sam.xlsx (8.4 KB)
Workflow:
Sample.xaml (11.9 KB)

@arjunshenoy not able to open the workflow its saying document is invalid. Could u plz send screenshot of xaml activities

@Neha_Aggarwal1 , Hope you understand the flow.

1

2

3

4 - Properties of the inside ForEach

If you can implement the same, please mark this as a solution. Cheers.

A very classic modelling:


Properties of the for each activity:
grafik

@arjunshenoy could u plz paste the assign value(rightside) in the image 1 and 2

@Sudharsan_Ka


as seen in the image output panel there is Comment column name available but it throwing error

Can you check do you have column named Comment in the datatable ? @Neha_Aggarwal1

If you have , Please check that column whether you have extra spaces in the column name

@Neha_Aggarwal1

Please find the expressions.

dt_Up = dt_dataTable.Clone
dataRowArray = CurrentRow.ItemArray
dataRowArray(index) = “NA”

Cheers