If Cell Empty or Null Replace to Zero AsEnumberable

[Shyam_Pragash]

Hi.

i tried to replace to zero if cell Empty or Null using AsEnumberable without losing other columns data…

i got error.

Dt_ReadRange.AsEnumerable().Where(Function(r) r.ItemArray.Any(Function(a) string.IsNullOrEmpty(a.ToString))).Count>0

Thanks
Shyam

[ashwin.ashok ((TheCodingTheory))]

Hi @Shyam_Pragash ,

Could you try this and see if it works out for you?
Assign →

Dt_result = dt.Clone()

(From row In dt.AsEnumerable()
Let lst = row.ItemArray.ToList()
Let ra = lst.ConvertAll(Function(a) If(IsNothing(a) OrElse String.IsNullOrEmpty(a.ToString),"0",a))
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

You might want to save it to another datatable since there is a change you could run into the Collection Modified error.

Kind Regards,
Ashwin A.K

[varunk (Varun Kumar)]

Before replacing the data with zero, check whether the cell is empty or not If cell contains no data then write data as Zero.

Try this it will help you

[Shyam_Pragash]

Hi @ashwin.ashok

i got error output…

[ashwin.ashok ((TheCodingTheory))]

Hi @Shyam_Pragash ,

Sorry, my bad could you try this?

(From row In dt.AsEnumerable()
Let lst = row.ItemArray.ToList()
Let ra = lst.ConvertAll(Function(a) If(IsNothing(a) OrElse String.IsNullOrEmpty(a.ToString),"0",a)).ToArray()
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

Also ensure Add Headers is checked.

Kind Regards,
Ashwin A.K

[Shyam_Pragash]

@ashwin.ashok

(From row In dt.AsEnumerable()
Let lst = row.ItemArray.ToList()
Let ra = lst.ConvertAll(Function(a) If(IsNothing(a) OrElse String.IsNullOrEmpty(a.ToString),"0",a))
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

Hi @ashwin.ashok

Thanks