Conversion of string into integer from data table

dt.Select(“convert.ToInt32(Ageing>7)”),
I am triying to convert the data type of column of a data table using this excel query but it s not converting the values of a column named Ageing.

Hi @Ayush_Mishra

See this

Thanks,
Prankur

Try using Filter Datatable Activity.

image

image

@Ayush_Mishra
Try like this

dt.Select("Convert(Ageing,System.Int32)>7")

Regards,
Mahesh

1 Like

I’m wondering will that solution throw an error if a value is not a number though?
Typically, you will want to check the value before converting, but am wondering if that way works with no errors.

An alternate, and to me an easier to understand, method would be to filter the table as an enumerable and use the .Where() method.

dt.AsEnumerable.Where(Function(r) If(IsNumeric(r("Ageing").ToString.Trim), Convert.ToInt32(r("Ageing").ToString.Trim) > 7, False).ToArray

Instead of .ToArray you can use .CopyToDataTable, but I will usually use .ToArray when I want to process items in a dataset but keep the entire dataset still there.

In english it basically says, “Go through each row in dt, and if the value is a number, then compare with 7 and select row, if it is not a number then don’t select row… convert to Array to process only the rows that meet the criteria”

Regards. @Ayush_Mishra

4 Likes

@ClaytonM I have some questions on this. I can understand what you are doing with this line of code; however, this wouldn’t work as a value in the Filter Table Activity, right? How would you best use this?

This would not work for the Filter Data Table, because the options assume the value is a certain type, so any conditions or manipulations on the value is not possible.

For the Excel Filter Table,
I have not really used this, but I believe it is a simulation of the Filter feature in Excel. However, I can not find an example of using this for Greater Than or Less Than

You can try using {">0"} in the FilterOptions property. - not sure if it works

You can look at this example, though it only shows filtering by string values:

Anyway, I do not think you can apply complex logic to the Filter Table in expression form. However, I believe you can use wildcards for string values, like {"=*abc"} would be ‘Ends With’.


If the Robot is processing data, then typically you would want to create an array of the rows, so you can execute the process on each item while keeping the original data. Using .Where() as shown previously allows you do accomplish this more freely.

However, one thing you can try is using the Filter Table before the Read Range and check the ‘UseFilter’ property, but when you update the items, you would need to Read the original source data again unless you want to lose the items that were not included with the filter. - The Filter Data Table does this similarly.

So there’s some additional thoughts on that that.

Regards.

2 Likes