Filter data table without Case sensitive values

Help
, , ,
1

I have a table containing 4-5k rows with 9-10 columns. I just want to filter the table into another table. I am just getting the problem when any row is having value in different case which is other than filter criteria I have mentioned. for example - one column Status is having values
‘Single’, ‘single’, ‘SINGLE’, ‘married’,‘Married’, MARRIED’ and another column Country is having values like ‘usa’, ‘USA’, ‘Usa’, ‘Australia’, ‘australia’, ‘AUSTRALIA’. So I would like to fetch the values who are Single or SINGLE or single and living in usa or USA or Usa.
pls let me know how can i do that.

2 Likes
2

Hi @vikramgera

Welcome to our Community!

Convert the column as ToLower or To Upper then do your own logic

Ex
dt.Columns[“ColumnName”].Caption = dt.Columns[“ColumnName”].Caption.ToLower();

1 Like
3

@vikramgera

Welcome back to the uipath community.

  1. First use Read Range Activity to read the data from excel file and will give you output as DataTable and say ‘inputDT’.

  2. And set case sensitive to false for that DataTable like below.

    In Assign, inputDT.CaseSensitive = False

  3. And then use Filter DataTable Activity or select query to filter data from input data.

    newDT = inputDT.Select(“Status = ‘single’ AND Country = ‘usa’”).CopyToDataTable

7 Likes
4

Hi
Welcome back to uipath community
—we got two options to handle this
—once after getting the datatable use OUTPUT DATATABLE Activity and lass the datatable as input and get the output with a variable of type string named out_strdt
—then use a assign activity like this
out_strdt = out_strdt.ToUpper
—now use a GENERATE DATATABLE activity and pass this string variable as input and get the output with a variable of type datatable named Finaldt
—then we can use Filter Datatable activity and pass the input Finaldt and mention the filter condition value in all upper case
Or

We can use For each row loop and pass the datatable as input
—and change the specific column we are going to filter as a upper case like this

row(“yourcolumnname”) = row(“yourcolumnname”).ToUpper

And then apply filter datatable
Cheers @vikramgera

3 Likes
5

Hi,
thanks for your reply. However, Filter data table activity does not give the desirable result after assigning CaseSensitive property to False.

1 Like
6

@vikramgera

Ok. Have you tried with Select Query or not ? I mentioned select query in my previous post. Please give it a try once.

7

Yes Select query works But I wanted to use Filter data table activity as there are many filter criteria for multiple output tables in my process. Anyways, thanks I will try to consider select query for all output tables

1 Like
8

@vikramgera

Glad to help you. Happy Automation :grinning:

9

One more thing, If I have few Columns with same name like I have 2 columns with ‘Status’ column name then how can I apply select query with Column index.
PS: I am fetching the excel data by removing check on Addheaders property in read range

10

@vikramgera

Try like this:

  yourDT.Select("[Column-0] = 'value' AND [Column-1] = 'value'").CopyToDataTable
11

Yeah Thanks Man. Just little change, instead of [Column-0] and [Column-1], [Column0] and [Column1] will work. But many thanks.

1 Like
12

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