How to filter a column in Excel

Hello,

I have an excel file and a column with with some numbers and at the end of the list I have #N/A, like this:

test2

My question is, how do I configure the robot to filter that column to show only #N/A values?

The column name looks like this: test

I tried the following methods:

Read Range > Output to Dt

Assign dt to
Dt.Select("[Avaya_ID_in_Group__Col_E_???] = ‘#N/A’").CopyToDataTable()
Dt.Select("[Avaya ID in Group] = ‘#N/A’).CopyToDataTable()
Dt.Select(“Avaya ID in Group (Col E)???” like ‘#N/A’ “).CopyToDataTable()
Dt.Select(”[Avaya_ID_in_Group] = ‘#N/A’).CopyToDataTable()
Dt.Select("[L3] = ‘#N/A’").CopyToDataTable()

L3 being the cell position on the sheet.

None of the assigned values above worked, most of the time I got the error [“Cannot find column]

Any ideas on how I should fix this please?

Thank you.
Alex

Use Filter Table Activity.

image

See this similar thread:

Nadim,

I tried that but I get error [“Cannot find column]

Vivek,

The file i’m working with is read-only and a table name is not defined for that data. I cannot create a table

Hey Alex,

you need to Assign Column name

Aditya

Dt.Select(“[Avaya ID in Group (Col E)???] like ‘#N/A’ “).CopyToDataTable()

Please try this.

Nadim,

I got the error: the expression contains invalid date contact “#N/A”

Great :slight_smile:

Hi @AlexMt,

follow the thread below, it’ll help you.

only use FilterOptions as—> {“”}
that’s it :slightly_smiling_face: