Getting Error when reading column data in excel

Help Activities
, ,
1

I have excel data where C column is formatted with currency
and have converted the column C type into double
and iam doing sum of other 2 columns A and B and wring the value in C column

but when iam reading C column Value it is throwing error as conversion from sting to type double in invalid
can anyone tell what is going wrong?

Forum Engagement Daily Reports
2

Hi @T_Y_Raju

may be the column c values contains null value or non integer value in order to avoid that you can use if condition in your variable

if yourvariable isnot nothing AndAlso Integer.TryParse(variable.ToString, Nothing) Then

End if

Hope it helps!!!

3

actually, the values in column A and B is value separated with comma
like this $6,705.08
$10,165.20

4

the values which iam trying to sum have prefixed $ how can i sum this

5

Hey @T_Y_Raju

You can solve your query using LinQ,

Take a Assign Activity,
dt_Output = dt_Input
Note: dt_Input is Datatable Variable of Read Range, dt_Output datatype is also Datatable.

Using below mentioned LinQ in assign activity,
Left Side: dt_Output

Right Side:

From row in dt_Input
Let Total_Sum = CDbl(row("ColumnA").ToString.Replace("$","").Replace(",","")) + CDbl(row("ColumnB").ToString.Replace("$","").Replace(",",""))
Select dt_Output.Rows.Add(row("ColumnA").ToString,row("ColumnB").ToString,Total_Sum.Tostring)

Using Above LinQ, you will get final output in dt_Output variable as expected!

Regards,
Ajay Mishra

6

then you can use replace syntax , in yourvariable.replace(“$”,“”) then you can sum

7

is there way to sum the 2 columns data without replace

8

@T_Y_Raju

Can you please show how are you summing them?

Cheers

9

Use Above mentioned LinQ, it is not replacing the actual data with in datatable!

Regards,
Ajay Mishra

10

image
image

image
image460×546 35.4 KB

here iam using for each row in datatable adding the columns

11

@T_Y_Raju

as per error the blank values could not be converted to string if you want to consider blank as 0…then try the below

If(Double.TryParse(Currentrow("Base1").ToString,0),CDBL(Currentrow("Base1").ToString),0)

cheers

12

Actually iam try to sum of BG BI and BJ columns

13

let me try and check

14

Hi Anil when i used this method Total column row values are coming zero its not summing up

here i want to add the 3 columns data write it on Total column , Plz check once

15

@T_Y_Raju

Can you show shcreemshot of what you implemented

Cheers