I Need To filter excel data column by using LINQ query

Hi All,

I Need to filter the excel file based on both two values (D and E) by using the Linq query.
Can anyone please help me?

ex- I have a column with the header name “Queue”

I need to filter the rows if D and E both values exist in the Queue column.

Hey

Try with the following:

dtOutput = dtInput.AsEnumerable.Where(Function(x) x("Queue").ToString.Trim.ToUpper.Equals("D") And x("Queue").ToString.Trim.ToUpper.Equals("E")).CopyToDataTable

Regards

1 Like

Hi @Ankit_Chauhan ,

If the requirement is to filter rows where Column “Queue” can either contain value D or value E. Then we could use the below Query :

DT.AsEnumerable.Where(Function(x) x("Queue").ToString.Trim.ToUpper.Equals("D") OrElse x("Queue").ToString.Trim.ToUpper.Equals("E")).CopyToDataTable

Check the below post for Handling errors related to Direct CopyToDatatable :

I can not use orElse because I want data based on both values D and E. so if I’m using And condition then it’s showing error.

It’s not working, showing error like - “Assign: The source contains no DataRows”

Hey @Ankit_Chauhan !

Just to understand, do you have a DT like this?
image

And you want to get all the rows where the Column “Queue” contain the value “D” or “E”?

DT.AsEnumerable.Where(Function(x) x("Queue").ToString.Trim.ToUpper = "D" Or x("Queue").ToString.Trim.ToUpper = "E").CopyToDataTable

The output is:
image

Yes, the Data table is fine and It’s working with the OR condition but I want to add AND condition instead of the OR condition.
Do you have any solution?
Please reply.

If you want to work to “D” AND “E” so your Queue will never have a value if it only has 1 character “D” OR “E” OR “AnyOtherCharacter”

Your table can contain both values “D” AND “E” inside each row at the Queue column!?
Like this:
image
In that case you want the values marked in red, right?

If thats the case you can use the .Contains() inside the LINQ, like this:

DT.AsEnumerable.Where(Function(x) x("Queue").ToString.Trim.ToUpper.Contains("D") and x("Queue").ToString.Trim.ToUpper.Contains("E")).CopyToDataTable

image


If you want to use the AND operator to check a Column that only has 1 character you'll never get any value.
If it doesnt work, can you provide an example of your table?

I have a data table like this

image

and I have to filter all the data based on two values “Interface to EBS” and “Cancelled” in the Queue column.

I mean I have to filter data when both values exist(“Interface to EBS” and “Cancelled”) in the Queue column.

dtOutput = dtInput.AsEnumerable.Where(Function(x) x("Queue").ToString.Trim.ToUpper.Equals("INTERFACE TO EBS") OrElse x("Queue").ToString.Trim.ToUpper.Equals("CANCELLED")).CopyToDataTable

Regards

1 Like

So you need to take all the row based on these two scenarios “Interface to EBS” and “Cancelled”. In other case you don’t get the rows, right? For example, if you have a value like “Pending” you want to ignore it and take only the first two cases, right?

If that’s the case you will need to work with the OR operator because your row will never have the “Interface to EBS” and “Cancelled” inside of it.

Your query need to be created thinking in a row to row scenario. Or your Queue will have the value “Interface to EBS” or “Cancelled” or “AnotherString”

So the approach with Or that I mention earlier will solve your case

Sorry, I did not get your point.

You only use AND to check two true cases

Your first row from your column Queue will never have “Cancelled” and "Interface to EBS” inside of it. Your row will have “Cancelled” or "Interface to EBS” inside of it.

So the query will read the value from your Queue column based on the first row of your table. If it has “Cancelled” or "Interface to EBS” all the row will be stored.

Then the query will check the second row from your table and if the value in the Queue column has a string “Cancelled” OR "Interface to EBS” all the row will be stored in your datatable variable. If the value from your Queue isn’t what you expect, the row will be disconsidered.

The asenumerable will loop for every row of your table, checking the value that you passed based on your column name

@fernando_zuluaga posted the code to solve your question

Thanks, I got it, But to solve with AND condition, what should I do?