Replace empty cell with 0 using LINQ

Hi Everyone,

I have table in excel with 3 columns some of cell is empty how I can replace empty cell with 0 using LINQ .

I have read this topic but I got error can any one guide me to do this .

LINQ query to update(replace)datatable in uipath

Thanks

@ppr

@Dev2
can you please share more details to the error that you have got. What was done so far?

@ppr

I have dateable , and I put assign with this code

(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()

it is correct ?

@ppr

Compiler error(s) encountered processing expression “(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()”.
’Rows’ is not a member of ‘UiPath.Core.GenericValue’. Sequence.xaml

This the error

@Dev2
find some starter help here:
Replace_AllCellsAllRows_EmptyNothingWith0.xaml (7.3 KB)

ensure the correct datatypes. The issue from above comes from the wrong datatype; generic value. dtCorrected has to be of datatype Datatable

1 Like

Thanks i will see that

it could be dynamic so if we have any number of coalman’s and rows ?

Yes, it is dynamic and look in every column and every row for it

@ppr

dtData.Rows(2)(0)

this mean row 2 and 0 ?

@Dev2

the syntax: dtData.Rows(2)(0) means: third row from datatable first column

yes that what I mean , it is possible if we do not specify the row ? as dynamic

didn’t get you. What is needed, what is the requirement?

I wrote 3 Colmans only for example, so Mabey the coulman number change now I have 3 coulman but Mabey we have more… are you geating me?

as the statement runs dynamicly over all rows and columns it is already fully dynamic.

in case of all columns for a particular row are to handle then give a try

Assign activity:
left side: datarowVarCorrected (DataType: Datarow)
right side

dtData.Rows(2).ItemArray.Select(Function (e) If(isNothing(e) OrElse String.IsNullOrEmpty(e.toString), 0, e)).toArray
1 Like

Thanks

@ppr
sorry it is not work with me , i have write range to get result but it is not give me the correct output .

@Dev2
please share as much details with us, so we have a chance to know what was done and what it is the issue. Thanks

Okay , for example I have

co1- col2 - col3

3 6

4 8

             5           8

So I have this value but some of the cell empty how I can write 0 in the empty cell

what is implemented so far? Can you share XAML or some screenshots along with details?
Data comes from excel?

Thanks