Linq to math round 3 decimals column of string

[dyuonn.bakker (John Bakker)]

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

[Palaniyappan (Forum Leader)]

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

[dyuonn.bakker (John Bakker)]

@Palaniyappan

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()

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

[dyuonn.bakker (John Bakker)]

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

[Palaniyappan (Forum Leader)]

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

Convert data in columns into having 2 decimal places (possibly using LINQ)

Hi, I want to change the data gathered from a csv file into having 2 decimal places at the end. This is because I have to compare them with an extracted datatable, which holds the same value but with proper 2 decimal places because it is based on the currency format (but I don’t think it is saved as such). For example: Extracted Datatable: 45.80 1232.60 CSV Datatable: 45.8 1232.6 The amounts with no cents at the end have no problem though. If there’s no way to exclude whole numbers like …

Cheers @dyuonn.bakker

[dyuonn.bakker (John Bakker)]

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

[dyuonn.bakker (John Bakker)]

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