Sorting a datatable based on numbers stored as strings AND Add Data Row error

Dear experts,

  1. 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?

  2. 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.")

Any help would be greatly appreciated!

Thanks in advance!
TKG

Hi @tkargee

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.

Check it out.

SortNumericDataTable.xaml (9.5 KB)

I guess this is what you are looking for…

Let know whether this helps to get your problem sorted.

If it works for you, please make sure to mark the answer as the solution too :slight_smile:

1 Like

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

Also, check out these search results on possible other similar methods: Search results for 'sort defaultview' - UiPath Community Forum

Regards.

4 Likes

Hi @ClaytonM
Good to hear from you!! :smiley:

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.

SortNumericDataTable.xaml (7.8 KB)
CheckFile1.xlsx (9.7 KB)

However, there can be many ways to do this. And your answer could be much efficient than mine because I have a loop involved and yours is quite straight forward :slight_smile:

And I also learned something new again from you!! :smiley: Thank you for sharing bro!!

2 Likes

Hi guys,

Thanks for the suggestions!

I’m using the method suggested by @ClaytonM as my source datatable is not a pre-defined one nor from CSV or Excel, but a generated datatable in my sequence.

The only change I needed to make was to change it to OrderByDescending since I needed the list to be sorted in descending order.

Thanks both for your suggestions! Really appreciate it!

PS. Do you happen to know the answer to my second question, i.e., Adding Data Row with a 1-d array of integers to a datatable?

Thanks!
tkargee

1 Like

Can you show how you have set up your Add Data Row and ArrayRow property, so I can try and reproduce your error? Seems you have a type issue, like it is expecting objects instead of integers.

Hi @ClaytonM,

I have a Build Data Table activity defining the datatable dtRowDiv as 1 column with type Integer.

My ArrayRow (arr_LineNum) property is defined as Int32, default value [0].

Assign arr_LineNum(0) = RowNum

Add Data Row:

ArrayRow: arr_LineNum

Datatable: dtRowDiv

Please refer to attached image for error message,

Thanks in advance!


Cheers!
tkargee

Hi.

Since you are using an Int array variable, it’s not liking it. There are a few things you can try.

  1. just cast your variable to an Object array, like this:
    arr_LineNum.Cast(Of Object()).ToArray

or 2) change arr_LineNum to an Object array, however, some of your other coding will need to change to convert the object to integers when needed.

Regards.

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