Linq to math round 3 decimals column of string

Hi experts,

Quick question from my side, is it possible to use a linq statement to round all values in a string column to 3 decimals points? I have already changed the commas to points using a link statement but now I need to format it to meet our input requirements. For each row is not a possibility here as we work with very large data sets.

Would be great if someone could help me out here.

Many thanks, john

1 Like

Hi

Hope this expression in assign activity would help you resolve this

dtData = (From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) Convert.ToDecimal(x.ToString.Trim).ToString(“F3”)).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

Or

dtData = (From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) Math.Round(Convert.ToDecimal(x.ToString.Trim),3)).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

Cheers @dyuonn.bakker

2 Likes

Hi Palaniyappan,

Thanks for your reply, how do I specify which column is set to the format/ selected to set to an array?

As I understand, dtData is input Datatable, Dtcorrected is output datatable.
The array with the changed values (format) is added to the output datatable.

Kind regards,

John

Hi Palanuyappan,

The following should work, correct?
dtData = (From r In dtData.AsEnumerable
let ra = r.ItemArray.Select(Function (x) Convert.ToDecimal(x(“ColumnName”).ToString.Trim).ToString(“F3”)).toArray()
Select dtCorrected.Rows.Add(ra)).CopyToDataTable()

But I receive the error, stict on disallows late binding.
Any thoughs on how to select the specific column here?

Kind regards,

John

1 Like

Fine
Got to see a thread with similar scenario
Have a view on this

Cheers @dyuonn.bakker

1 Like

Hi Palaniyappan,

Thanks for your feedback, really helpfull indeed! Runs now in about 5 secondes instead of using the standard uipath activities looping trough the data took over 3 hours with the assign activities.

I got it resolved using the following block of code
Just out of curiousity, if i wanted to make the column names dynamic as well, how should i then loop both the column stored in an array and the rows, so not using the same lines of code 6 times over:

dtTest.AsEnumerable().ToList().ForEach(Sub(row)
If String.IsNullOrEmpty(row(“Risiko aktuell ab”).ToString)
Else row(“Column”)=DateTime.ParseExact(row(“Column”).ToString,“MM/dd/yyyy 00:00:00”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)
End If
row(“Column1”)= math.round(Decimal.Parse(row(“Column1”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
row(“Column2”)= math.round(Decimal.Parse(row(“Column2”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
row(“Column3”)= math.round(Decimal.Parse(row(“Column3”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
row(“Column4”)= math.round(Decimal.Parse(row(“Column4”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
row(“Column5”)= math.round(Decimal.Parse(row(“Column5”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
row(“Column6”)= math.round(Decimal.Parse(row(“Column6”).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
End Sub
)

Kind regards,

John

Hi Palaniyappan,

Also made the columns dynamic, thanks for the help!
See code below:

dtTest.AsEnumerable().ToList().ForEach(Sub(row)
If String.IsNullOrEmpty(row(“Risiko aktuell ab”).ToString)
Else row(“Columname”)=DateTime.ParseExact(row(“Columnname”).ToString,“MM/dd/yyyy 00:00:00”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)
End If
For Each Item As String In Arrstr
row(Item)= math.round(Decimal.Parse(row(Item).ToString.Replace(",",".").Trim, System.Globalization.NumberStyles.AllowDecimalPoint),3).ToString
next
End Sub
)

Kind regards,

Dyonn Bakker

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.