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))
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.
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.
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.
(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
Hi, Now getting new error.
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.
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.
My Assign activity code for your review.
Can you share me your xaml with zipped file.
Hi Vicky, I’m unable to view your post.
Hello @BaskaranVenkatesan
Decimal Zip.zip (8.6 KB)
Also Try this and let me know about this code
(From x In UnitPriceDT.AsEnumerable() Select UnitPriceDT.Clone.Rows.Add({CStr(If(CStr((x("Unit Price"))).Contains("."),CStr(x("Unit Price")).Split("."c)(0)+"."+CStr(x("Unit Price")).Split("."c)(1).Substring(0,2),x("Unit Price"))) })).copytodatatable
Anil_G
ashokkarale
kouhei_yamaguchi
ppr
Nabin_Timalsina
sharazkm32
banksmegacorp
alexis.mendoza.rpa
m.takamiya
Honda