Dear Friends, I am using the below code
Dt_billnotlodged = (From row in out_dt_final1021.AsEnumerable.Where(Function(x) x (“Remarks”).tostring.tolower.contains(“bill not lodged”))
Group row by a =row(“Trim”) into grp =group
Let xsum =grp.sum(Function (x) CDbl(x(“Final Reportable in INR”). tostring.trim))
Select Dt_billnotlodged.rows.add({a,xsum})). copytodatatable
When I am using this I am getting error as conversion from string “” to type double is not valid.
(From row In out_dt_final1021.AsEnumerable.Where(Function(x) x ("Remarks").tostring.tolower.contains("bill Not lodged"))
Group row By a =row("Trim") Into grp =Group
Let xsum =grp.sum(Function (x) If(Double.TryParse(x("Final Reportable In INR").tostring.trim,New Double),Double.Parse(x("Final Reportable In INR").tostring.trim),0))
Select Dt_billnotlodged.rows.add({a,xsum})). copytodatatable
The error shows there is empty string and failed to convert to double type.
So, i think it’s no problem if we handle empty string as 0 because it’s Sum function.
If you have any concern, can you share it?
|Column 1 | Column 2 | Column 3 | Column 4|
|Remark | Trim | Final Reportable in INR| Date|
| Bill not lodged|123 | 0 |21-10-2022 |
| Bill compared| 456 | 56 |21-10-2022 |
| Bill complete| 543 | 76| 21-10-2022|
|Bill not lodged | 567 | 0 |21-10-2022 |
| Bill compared| 987| 54|21-10-2022 |
| Bill not lodged| 566 | 34 | 21-10-2022|
My excel looks something like this here I want Trim and Final Reportable in INR values
Dt_billnotlodged = (From row In out_dt_final1021.AsEnumerable().Where(Function(x) x("Remark").ToString().Contains("Bill not lodged"))
Group row By a = row("Trim") Into grp = Group
Let xsum = grp.Sum(Function(x) If(Double.TryParse(x("Final Reportable in INR").ToString().Trim(), 0.0), CDbl(x("Final Reportable in INR").ToString().Trim()), 0.0))
Select Dt_biklboylodged.Rows.Add({CInt(a), CInt(xsum)})).CopyToDataTable()
=> Write Range Workbook Dt_billnotlodged back to excel
Got it your issue is in line number 1 or one, when BOT is reading this file it taking the first row as header and the second column 2 contain string (Trim)and value (134).
you need to delete the first row and make your data look like below read the first row as header
that why it is not able to convert the string to Double
