Excel Issue wrong format

Help Studio
, ,
1

Hi, I have written a linq query for groupby function with the help sum function
this is the Linq query
(
from row in dt
group row by a=row(“Column1”),b=(“Column2”) into grp=Group
Let xSum=grp.Sum(Function(x) cdbl(x(“Column3”).tostring)
Select Output_Dt.rows.add(a,b,Xsum)
).copytoDataTable
when I’m executing it. It is executing this error “Assign: Input string was not in a correct format.”

2

Hi,

There might be non numeric value including empty.
Can you try the following expression?

(
From row In dt
Group row By a=row("Column1"),b=("Column2") Into grp=Group
Let xSum=grp.Sum(Function(x) If(Double.TryParse(x("Column3").tostring,New Double),Double.Parse(x("Column3").tostring),0))
Select Output_Dt.rows.add(a,b,Xsum)
).copytoDataTable

Regards,

1 Like
3

Hi,

In your LINQ query, you are attempting to convert the values in “Column3” to Double using CDbl(), and this error is likely happening because there are some non-numeric values in “Column3” that cannot be converted.

To handle this issue, you can modify your LINQ query to first filter out rows where “Column3” contains non-numeric values before attempting the conversion.

you can use below query

(
From row In dt
Where IsNumeric(row(“Column3”).ToString()) ’ Filter rows with valid numeric values in Column3
Group row By a = row(“Column1”), b = row(“Column2”) Into grp = Group
Let xSum = grp.Sum(Function(x) CDbl(x(“Column3”).ToString()))
Select Output_Dt.Rows.Add(a, b, xSum)
).CopyToDataTable()

1 Like
4

in this I’m getting another error.
Select Output_Dt.Rows.Add(a, b, xSum) in this line because I’m adding a row which can’t use by group by function. Select Output_Dt.Rows.Add(a, b, xSum, row(“Unmatched column”) ) . Its throwing an error i.e, row is not in scope how can I Declare it. how can I declare it?