Reduce the decimal points in Excel

Hi All,

Good Day!

Using Data Scraping method, i have scraped some details from web application and write it as Excel file. In that excel i have Price amount like as 1343.305, 1234.1234 in price column. i want to reduce the decimal points to 2 numbers like 1334.30 for entire column. When i tried the below method using assign activity, getting error in column name. Kindly advise how to fix it.

String.Format("{0:0.00}", row(Columnname))

1 Like

Hello @BaskaranVenkatesan
You can use linq here
use this code
(From x In DT.AsEnumerable() Select DT.Clone.Rows.Add({CDbl(x("Price").ToString.Split("."c)(0)+"."+x("Price").ToString.Split("."c)(1).Substring(0,2))})).copytodatatable

@BaskaranVenkatesan add (0,2) in the substring field
Check this workflow for better understanding
DecimalLQ.xaml (7.1 KB)

Hi Vickydas,

Thanks for your prompt response. I’ll check and let you know if have any doubt.

Hi @BaskaranVenkatesan

we can simply use a assign activity
out_str = math.Round(Convert.ToDouble(in_str),2)

then we can use this out_str to enter into excel
Cheers @BaskaranVenkatesan

Hi All,

After write in Exce file I had read the same File and stored it as DT1. Then using assign activity i have applied above code. After that what can i do for change decimal point in excel. Please explain clearly.

Hello @BaskaranVenkatesan
Did you check the workflow?

the above code will make the required changes in your price Column
All you have to do is
add other column Name so for instance if you have two columns Named as ID & Price
than your code should look like this

(From x In DT.AsEnumerable() Select DT.Clone.Rows.Add({(x("ID"),CDbl(x("Price").ToString.Split("."c)(0)+"."+x("Price").ToString.Split("."c)(1).Substring(0,2))})).copytodatatable

thats it you just have to add your column names inside the Curly Brackets in this format
x(“ColumnName”)
Than Assign this new value to Datatable Type Variable and write it in an Excel File

Hi Vicky,

When i open the workflow an error message was occurred like Missing or invalid activity.

Hello @BaskaranVenkatesan
What version are you using ??
You might be using an older version

Anyways follow these steps

When you read your file you’ll get an output variable named as DT1
take an Assign Activity below the read range and assign the code like this

DT1 =
(From x In DT.AsEnumerable() Select DT.Clone.Rows.Add({CDbl(x("Price").ToString.Split("."c) (0)+"."+x("Price").ToString.Split("."c)(1).Substring(0,2))})).copytodatatable

and than take a Write Range Activity and use that output variable as an input in that Activity

Let me know if you did’nt get it

Hi, I’m using 2019.08 version. ok will check the above method and get back if have any concern. Thank you!

Hi Vicky,

I had tried above method but getting an error like as below. Please advise.
image

(From x In UnitPriceDT.AsEnumerable() Select UnitPriceDT.Clone.Rows.Add({CDbl(x(“Unit Price”).ToString.Split(".“c) (0)+”."+x(“Unit Price”).ToString.Split("."c)(1).Substring(0,2))})).copytodatatable

Hello @BaskaranVenkatesan
Try this code
(From x In UnitPriceDT.AsEnumerable() Select UnitPriceDT.Clone.Rows.Add({cdbl(if((CStr(CDbl(x("Unit Price"))).Contains(".")),(CDbl(x("Unit Price").ToString.Split("."c) (0)+"."+x("Unit Price").ToString.Split("."c)(1).Substring(0,2))),(x("Unit Price")) ))})).copytodatatable

Hi, Now getting new error.
image

Hello @BaskaranVenkatesan
two Questions
What Datatype of value you are assigning this code to ?
is the code properly copied ?

(From x In UnitPriceDT.AsEnumerable() Select UnitPriceDT.Clone.Rows.Add({cdbl(if((CStr(CDbl(x("Unit Price"))).Contains(".")),(CDbl(x("Unit Price").ToString.Split("."c) (0)+"."+x("Unit Price").ToString.Split("."c)(1).Substring(0,2))),(x("Unit Price")) ))})).copytodatatable

Compare the code if its correct or not
Download this workflow and check it
Decimal.xaml (6.6 KB)

Hi, Yes i had copied the code correctly. First i had read the Excel and and stored it as UnitPriceDT. Then using assign activity assign the code to UnitPriceDT. Then again tried to write the Same DT but this also getting the same error message like as “conversion from string”" to type ‘double’ is not valid. This time i had copied the assign activity from your xaml file but getting same error message. Please advise.

image

Hello @BaskaranVenkatesan
this will work definitely the error because the data is in string format and it cannot convert it to double

(From x In UnitPriceDT.AsEnumerable() Select UnitPriceDT.Clone.Rows.Add({cstr(if((CStr(CDbl(x("Unit Price"))).Contains(".")),(CDbl(x("Unit Price").ToString.Split("."c) (0)+"."+x("Unit Price").ToString.Split("."c)(1).Substring(0,2))),(x("Unit Price")) ))})).copytodatatable

Copy this and let me know

Hi Vicky, This time also getting same error like as previous one.

Hi Vicky, Any update.

Hello @BaskaranVenkatesan
I tried it just now and it works fine in my system try running this file in your system and let me know if it works ?

Decimal With XL .xaml (11.9 KB) Unit.xlsx (8.5 KB)

and also show me the code you are assigning in the Assign Acitivity

Hi Vicky, I had reviewed you Unit Excel, it like same as my column. When i tried to open your xaml file an error message was occurred like as below. This time also i had tried your assign activity but getting same error.

image
image
My Assign activity code for your review.


Can you share me your xaml with zipped file.