Need to filter a column in excel that starts with same first 2 numbers


#1

Hi All,

I have an excel file and I want to apply a filter to a column. Column name is sequence Id that having a 10 digit sequence id. I need to find the sequence ids which starts with 45xxxxxxxx.

Can anyone pls help me?

Thanks
Jose


#2

Hi,

Read the excel into a DataTable using ReadRange activity. Then you can do something like this

DataTable.AsEnumerable().Where(Function(row) row(“Sequence ID”).ToString.StartsWith(“45”))


#3

What variable type i can use while using the above syntax, since am getting error in datatype

I tried DataTable and DataRow variable types


#4

Depends upon what you wanted to do next.


#5

You may use the UiPath standard activity on data table as below. You have to first “Read Range” from excel and then apply filter by using “Filter data table” activity.

Change the column index as you want.


#6

I need to remove the sequence id which starts with 45 from excel.

I selected EnumerableDataRowCollections type for the above syntax. Now the error went off. But am not able to view the output its saying EnumerableDataRowCollections cannot converted into String.

PO_filter = InvoiceDt.AsEnumerable().Where(Function(row) row(“Purchasing Document”).ToString.StartsWith(“42”))


#7

I am using UiPath 2017 version. In this version filter data table was not available.

Any package that i need to install to get this activity?


#8

DataTable.AsEnumerable().Where(Function(row) row(“Sequence ID”).ToString.StartsWith(“45”)).CopyToDataTable()

This would get you the rows post filtering into a new DataTable. Then you can write that to a new excel using WriteRange


#9

I tried Write range and its saying that EnumerableDataRowCollection cannot converted into Datatable.


#10

The input to WriteRange should be the DataTable. You assign ouput of CopyToDataTable() into a new DataTable then use it subsequently in WriteRange