I’m reading in a GSheet into a new datatable in UiPath. The Google sheet has an “Amount” column. The column in GSheet is formatted as a number with 2 decimal places. When we read the GSheet into the DataTable, it pulls all the correct values accross. However, sorting the data table (after reading to DataTable) does not work. I think it’s not treating the column as a number, but rather as a string. I am digging through the UiPath docs, but not seeing a clear answer. I know several workarounds are out there (i.e cloning the datatable, setting the column to Double, and then copying the data). However, it seems like there has to be a better (simpler! Cleaner!!) way to do this. Can anyone tell me what I’m doing wrong here?! I extracted the logic to a simplified example for troubleshooting, and have attached screenshots below.
Here is the output. You can see it’s doing a string sort (1122.17 is before 142.00 and 142.00 is before 5.27). How do I tell the data table (or sort) that this column is a number and needs to be sorted as such?!
BEFORE SORT:
@"Amount,Status
104.78,Approved
1122.17,Approved
9.78,Approved
8.78,Approved
142.00,Approved
5.27,0,5.27,Approved
"
AFTER SORT:
@"Amount,Status
104.78, Approved
1122.17,Approved
142.00,Approved
5.27,Approved
8.78,Approved
9.78,Approved
"
I believe if you prepare the datatable first with Build Datatable, then you can control the datatype of the column so it’s Double. Or just Add Column as double, then For Each Row in Datatable and assign CurrentRow(“doublecolname”) = CDbl(CurrentRow(“stringcolname”).ToString) then remove the string column and sort on the double column.
Hi @postwick - Thanks for the response! I’ve just tried this again to be certain - and I’m getting the same result. Added a Build to the begining which assigns “Amount” to be a double field. Sort is still treating the field as a String sort.
Possibly the Build Datatable definition is being overwritten when you read the spreadsheet into it.
@postwick - Possibly. The only way that I have gotten this to work is to:
(1) Read the DataTable from GSheet (where column is correctly defined) to myOrigDT
(2) Clone the DataTable (essentially get all the column names in order) as myNewDT
(3) Set the datatype on the Amount Column myNewDT.Columns(“Amount”) = GetType(System.Double)
(4) For Loop (For Each Row In myNewDT) Import CurrentRow from myOrigDT to myNewDT.
This Can’t possibly be the best way. Code is ugly, and adds alot of overhead to new workflows. Can someone from UiPath weigh in on this?
You don’t need to do all that cloning etc.
Just use Add Data Column to add a Double column to myOrigDT. Then For Each Row through myOrigDT and assign CurrentRow(“DoubleAmount”) = CDbl(CurrentRow(“StringAmount”).ToString)
You can then remove the StringAmount column if you want.
To get it lighter can try using linq
dt = dt.AsEnumerable.OrderBy(function(x) If(IsNumeric(x("ColumnName").ToString),CDBL(x("ColumnName").ToString),0)).CopyToDataTable
cheers
Thanks for the suggestion here @Anil_G! This will work as well. But I continue to feel like this is more complex than it should be. It feels like there really should be a way to ensure the number format is pulled correctly from GSheet. For you and I perhaps it’s no big deal to fix (an extra few lines). But if the idea is for UiPath to be low code - something available to citizen development - this added complexity more than most will be able to understand (copy/paste, maybe…but they won’t understand what they’re doing here). Thanks again!
Agree with you on that…
But the data should be clean for it as well.
As leaving blanks is not considered as a numeric…hence you have to…
But in the sheet if all rows are filled and are numeric completely with no special characters then ideally the column type might be as needed(this is same with excel…if excel whole columnis number it auto casts to number)
Cheers
sharazkm32
prashant1603765
V_Roboto_V
ashokkarale
Stef_99