I Need To filter excel data column by using LINQ query

[Ankit_Chauhan (Ankit Kumar)]

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.

[fernando_zuluaga]

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

[supermanPunch (Arpan)]

@Ankit_Chauhan

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

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 :

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.AsE…

[Ankit_Chauhan (Ankit Kumar)]

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.

[Ankit_Chauhan (Ankit Kumar)]

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

[imloan (Haniel Loan)]

Hey @Ankit_Chauhan !

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

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:

[Ankit_Chauhan (Ankit Kumar)]

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.

[imloan (Haniel Loan)]

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:

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

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?

[Ankit_Chauhan (Ankit Kumar)]

I have a data table like this

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.

[fernando_zuluaga]

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

[imloan (Haniel Loan)]

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

[Ankit_Chauhan (Ankit Kumar)]

Sorry, I did not get your point.

[imloan (Haniel Loan)]

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

[Ankit_Chauhan (Ankit Kumar)]

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