Search In Excel Data Table using linq

[dutta.marina (rpa_06)]

Hi All,

I have a excel data table where columns are “Charges” and “ListPrice(Quantity)”

In my Queue transactions I have “SkuName” and “ListPrice”

I need to check if in my excel column values “Charges” is matching queue item value “SkuName”. and excel column value"ListPrice(Quantity)" matches sku item value “ListPrice”

I need to loop the entire data table and find a match if current row column values “Charges” is matching queue item value “SkuName” and current row column value “ListPrice(Quantity)” matches sku item value “ListPrice”

When ever a match is found do nothing else add the sku item

I have build a Linq Query

ExtractDataTable.AsEnumerable().

Where(Function(row) row.Field(Of String)(“Charges”)=sku_name AndAlso

row.Field(Of String)(“List price (Quantity)”) = sku_listprice).Count>0

But the problem is I am using for each row in Data Table,

Since in the first column its not finding match its going to else block and adding the queue item even though somewhere in bottom rows the match is found

How do I build the query and logic for this where its seraches in one shot entire data table and if match is found don’t add else add queue item

[Anil_G (Anil Gorthi)]

@dutta.marina

Why not use a filter datatable to check if match is found instead of for each

Another way if you want for eqch is use a boolean variable…set to true if found…on not found side do nothing…also you can use break on the true side sao that remaing rows neednot be checked

And after loop check if values is true then dont add else add or viceversa based on what you need

Cheers

[dutta.marina (rpa_06)]

@Anil_G

If I need to use filter data table in Linq , I am having one challenge .

Before I use filter data table for column values “Charges” and “ListPrice(Quantity)”

I first need to use the regex expression for “ListPrice(Quantity)” column to remove $ and USD and then use filter data table. Can I achieve all this in Linq Expressions. first regex expression for column ListPrice(Quantity) Regex expression and then filter data table to compare? I am unable to get the Linq expressions

“\d+.\d{2}\d*(?<=[1-9])|\d+.\d{2}”)

Like apply regex to column “List price (Quantity)” column and then use filter data table query to compare the column values

if column value List price (Quantity) =ListPrice (queue item) AND “Charges” = SkuName

THEN do nothing else add queue item

[Anil_G (Anil Gorthi)]

@dutta.marina

You should be able to use all in one

In where use AndAlso and other expressions you need

Cdbl(yourregex) > value you need can be one condition…like that add any condition uou need using andalso in same where condition

Cheers

[dutta.marina (rpa_06)]

@Anil_G

I want to combine these two expressions: Can you please help on combining the two expressions : First one is regex and second one I want to use filter data table

ExtractDataTable.AsEnumerable.Where(Function(x) System.Text.RegularExpressions.Regex.IsMatch(x(“List price (Quantity)”).ToString,“\d+.\d{2}\d*(?<=[1-9])|\d+.\d{2}”)).CopyToDataTable

ExtractDataTable.AsEnumerable().

Where(Function(row) row.Field(Of String)(“Charges”)=sku_name AndAlso

row.Field(Of String)(“List price (Quantity)”) <> sku_listprice).Count > 0

[Anil_G (Anil Gorthi)]

@dutta.marina

Try this

ExtractDataTable.AsEnumerable.Where(Function(x) System.Text.RegularExpressions.Regex.IsMatch(x("List price (Quantity)").ToString,"\d+.\d{2}\d*(?<=[1-9])|\d+.\d{2}") AndAlso x("Charges”).ToString=sku_name AndAlso x("List price (Quantity)").ToString <> sku_listprice).Count >0

Cheers

[dutta.marina (rpa_06)]

@Anil_G

I dont have to use for each row in Data Table?

Just add if condition and add the linq ?

[Anil_G (Anil Gorthi)]

@dutta.marina

Yes that should suffice

Cheers

[dutta.marina (rpa_06)]

@Anil_G

Its still going to then block. Ideally it should go to else block. There is a match

When I tried to debug and get the query is Immediate panel

[sharazkm32 (Sharaz K M)]

Try using invoke code method with following arguments

1. Data Table → Input
2. Charges → Input String
3. Price → Input Double/ Int / String
4. ou_RowCount → Output Filtered Row Count

Code to be pasted in the invoke code method.
ou_RowCount= dtInput.AsEnumerable().Where(Function(row) row(“Charges”).ToString()= in_Charges and row(“Price”).ToString= in_Price.ToString).ToList().count

Kindly note that - use regex and format price value before passing to this code segment. Once the code run is completed - get the output to a variable and check whether the count is greater than 1 or not

[Anil_G (Anil Gorthi)]

@dutta.marina

As per error one of the bracket is missing

Cheers