I am trying to sort a 1-d datatable in descending order based on numbers stored as strings (“94”, “158”, “222”). The “Sort Data Table” activity does not work as the output becomes (“94”, “222”, “158”). Do you have any suggestions for me?
I tried converting each entry to an integer (integer.parse), but I am unable to write the array into a new datatable with “Add Data Row” activity (error: “Value of type ‘1-dimensional array of integer’ cannot be converted to '1-dimensional array of Object” because ‘Integer’ is not a reference type.")
I have a sample workflow which I had created to do the same. In the initial build data table activity, there are set of rows that contain numbers, but in String format. Then, I have a set of activities that I use to insert these records to another datatable. However, in this datatable, the column is a numeric (Int32) type. Then I have a Sort data table activity which sorts the data as you require.
Just wondering, did you test this using an excel or csv file as input, because I noticed you are using a table with numeric values as input. From my experience, the Sort Data Table activity would look at the values as strings and therefore sort alphabetically. But, am willing to learn if there is a good way to sort the strings as numbers using that specific activity.
Here is one way I have sorted the table as an alternative @tkargee Assign activity: dt1.AsEnumerable.OrderBy(Function(r) If(IsNumeric(r("columnname")), CDbl(r("columnname")), 0) ).CopyToDatable
Yes, you are right, for string values, the sort data table activity will sort the numbers considering it as a string. Because of that, the numeric sort will not happen as we expect. I tried the same with a source excel file. However, if the excel file has the column data as numeric, it works fine and sorts in the ascending or descending order. But if the numbers are represented as string in the excel, then this will not work. So I used a loop to add the values to another datatable where the column is in number format So we can use the Sort Data Table activity to get the numbers sorted.