How to "filter" with StartsWith in a numeric column?

I use the “Filter” activity to group the rows that I require that depending on the values of a column will change the values of another but the first column has exclusively numeric values and the excel file that I use interprets that column as a numeric column and can not be filtered by “StartsWith”.

I thought another way which interacts directly with StudioX and the excel file, this way I thought was to go through each row without filtering, check the field of the numeric column using the “Left” function, compare it with a specific value and then change the value of the cell of the target column but the file that is generated (it is extracted from a web page) returns a high amount of rows, more than 2000 records, and when filtering from the excel file using search, it returns something more than 100 records and this would speed up the process.

It seems that the “Filter” activity is limited in that aspect and more because excel gives the option of “StartsWith” in “Number Filters” inside “Custom Filters” but returns nothing if this filter is applied.

Now yes, will there be a way to activate “Filter” but looking for the beginning of the text if the column of the excel file is interpreted as a numeric column?

By the way, the values in this column are numbers between 6 to 10 digits and although the excel file that is generated always returns numbers of 10 digits (the obvious thing is to compare if it is greater than a specific number and that’s it) but there may be the possibility of returning values of fewer digits and that is why I require the “StartsWith” to perform the “Filter” or if there is another way that can optimize the execution time of the bot, it would serve me very well.

Example of returning data: there are numbers that return with the initial 2100 and there are others that start with 2600, in this case I need to filter only those that start with 2600 regardless of the number of characters; I also thought of changing the data type of the column in excel but for some reason excel still interpreted that column as a number; there is another option and that is to place an apostrophe in front of each number but that would not optimize the execution of the bot, so what can I do?

Hi @Robinson_Osorio ,

If you are okay with looping through the row items, then you can convert the item to string and then apply the conditions inside an If Activity like so →

CurrentRow(ColumnNameOrIndex).ToString.StartsWith("2600")

image

If you are working with large datasets, then you might want to try using LINQ instead →

dt.AsEnumerable().Where(Function(w) w(ColumnNameOrIndex).ToString.Trim.StartsWith("2600")).ToList()

We are outputting it to a list first, because if the filter returns empty results, a DataTable will not accept it.

We will add the values into a List of DataRows and check whether its Count is greater than zero, after which we will push it into a Table->

dt.AsEnumerable().Where(Function(w) w(ColumnNameOrIndex).ToString.Trim.StartsWith("2600")).CopyToDataTable()

Kind Regards,
Ashwin A.K

I understand your answer but you do it based on a datatable, but can this be done directly from the excel file?

Hi @Robinson_Osorio ,

Sure that can be done using a for excel as well.
We usually read excel into a DataTable and then perform operations onto it, but it can be done either way.

Could you give it a try using excel and see if the logic works as expected?

Kind Regards,
Ashwin A.K