Replace Null or empty values with static value in a datable which has mutiple columns

Help StudioX
,
1

Hi

I want to replace Null or empty value with a static values(NA here) from a datable which has multiple columns.
it should replace all columns with one single LINQ query kindly assist.

2

Hi @karthik_kulkarni1

Read range

Assign dt=dt.AsEnumerable().ToList().ForEach(Sub(row) dt.Columns.Cast(Of DataColumn)().ToList().ForEach(Sub(col) If row.IsNull(col) OrElse String.IsNullOrEmpty(row(col).ToString()) Then row(col) = "NA"))

Write range

Hope it helps!!

3

Hi @karthik_kulkarni1

Use the below code in Invoke cod3e activity

' Assuming dataTable is the input DataTable
Dim modifiedDataTable As DataTable = dataTable.Copy()

For Each row As DataRow In modifiedDataTable.Rows
    For Each column As DataColumn In modifiedDataTable.Columns
        If row.IsNull(column) OrElse String.IsNullOrWhiteSpace(row(column).ToString()) Then
            row(column) = "NA"
        End If
    Next
Next

' Assign the modified DataTable to the Out argument
outDataTable = modifiedDataTable

Pass dataTable as in argument and map it with data table and outDataTable as out argument and create a new data table.

Use the created datatable in Write Range Workbook activity.

Hope it helps!!

4

Getting error as Datacolumn as ambigious

5

@karthik_kulkarni1

Try this:

' Assuming dataTable is the input DataTable
Dim modifiedDataTable As DataTable = dataTable.Copy()

For Each row As DataRow In modifiedDataTable.Rows
    For Each column As System.Data.DataColumn In modifiedDataTable.Columns
        If row.IsNull(column) OrElse String.IsNullOrWhiteSpace(row(column).ToString()) Then
            row(column) = "NA"
        End If
    Next
Next

' Assign the modified DataTable to the Out argument
outDataTable = modifiedDataTable

Hope it helps!!

6

Done Thanks it worked!!!

1 Like
7

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