I would like to replace all null values with DBnull in a data table

[manjesh_kumar]

Dear Experts,

I would like to replace all null values from a data table as DBnull.value the following link was to just to replace only one column. I used to write with different column names, however this is not possible since the number of columns more that 25 columns and it becomes a challenge, so appreciate your support with an extension of the link below.

Continuing the discussion from I am not able to insert records into the database where date column is null:

Appreciate your feedback/support.

Regards,
Manjesh

[Anil_G (Anil Gorthi)]

@manjesh_kumar

You can follow below steps

1. For each row in datatable on the main dt
2. For loop activity and give in argument as Enumerable.Range(0,dt.Columns.count).ToArray and change type argument to integer
3. Now use the if condition inside the loop isNothing(currentrow(currentitem) OrElse String.IsNullOrEmpty(currentrow(currentitem).toString.Trim)
4. On the then side you can replace with dbNull.value

Hope this helps

Cheers

[manjesh_kumar]

@Anil_G

can this be enhance on this code would be much easier.

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row)
If(IsNothing(row(“GRN_DATE”)) Or (String.IsNullOrEmpty(row(“GRN_DATE”).tostring.Trim))) Then
row(“GRN_DATE”) = DBNull.Value
End If
End Sub
)

Regards,
Manjesh

[Anil_G (Anil Gorthi)]

@manjesh_kumar

Here it is

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row)
For Each d In Enumerable.Range(0,in_dt_GrBlankAgeingdt.Columns.Count)
If(IsNothing(row(d)) Or (String.IsNullOrEmpty(row(d).tostring.Trim))) Then
row(d) = DBNull.Value
End If
Next
End Sub
)

Hope you know how to use in invoke code

cheers

[ppr (Peter)]

similar to:

LINQ query to update(replace)datatable in uipath

@Rachel7 this is maybe a too quick shot as it replace any space in any column to NA and returns a datatable. But you can give a try (From r In dtData.AsEnumerable Select ia = r.ItemArray.toList Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace(" ","NA")).toArray() Select dtCorrected.Rows.Add(ic)).CopyToDataTable() NEW VERSION, simplified: (From r In dtData.AsEnumerable let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace(" ","NA")).toArray() Select dtCorrected.Rows.…

dtCorrected = OriginDataTableVar.Clone()

dtCorrected =

(From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) If(isNothing(x) OrElse String.IsNullOrEmpty(x.ToString.Trim), DBNull.Value, x)).toArray
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

[manjesh_kumar]

Dear @ppr,

Sorry for delayed response, this solves my issue and thank you very much.

@Anil_G Thank you very much for pitching in

Regards,
Manjesh