If Cell Empty or Null Replace to Zero AsEnumberable

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

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

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

Hi @ashwin.ashok

i got error output…

image

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

Hi @ashwin.ashok

Thanks

1 Like

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