Having a doubt to rectify

Learn Academic Alliance
, , ,
1

Hello Everyone,

Let me explain my scenario.,

I have two different excel files Excel-A, Excel-B with different column headers.

What I have to do simply is compare between two datatable and remove the rows in excel B based on 4 column condition

Eg :
1st → I need to filter the excel -A and get only the rows that have “No” in the “Status” column
and this is done by filter datatable.
2nd → Now From Excel A (Get all the row values from 4 columns) and compare them with the Excel B rows and delete those rows if matched with those 4 columns in the Excel A

Note: Excel will have a dynamic number of rows may be little or many, So I have tried with For each row and it’s taking more time in my project and returning wrong output, Kindly give any suggestions in any method.

@Palaniyappan @lakshman @Srini84 @ppr @Yoichi @kirankumar.mahanthi1 @Nithinkrishna @codemonkee @supermanPunch

Thanks in Advance !!

Regards
Sudharsan

2

And Here is the sample xlsx file
Excel A.xlsx (7.9 KB)
Excel B.xlsx (8.6 KB)

Regards
Sudharsan

3

Give a try on following sequenceEqual approach:

Assign Activity:
LHS: Result | List(of DataTable)
RHS


(From d In dt1.AsEnumerable
Let cASet = {"dtACol1Name","dtACol2Name","dtACol3Name","dtACol4Name" } 
Let cBSet = {"dtBCol1Name","dtBCol2Name","dtBCol3Name","dtBCol4Name" } 
Let chs = cASet.Select(Function (x1) d(x1)).toArray
Where Not dt2.AsEnumerable.Any(Function (d2) cBSet.Select(Function (e) d2(e)).toArray.SequenceEqual(chs))
Select r = d).toList

then based on the filter result we can copy it to a datatable or clone the origin one
check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dt1.Clone

It should filterout dt1 rows where checked on the 4 cols no corresponding dt2 row is present

1 Like
4

we assume that
grafik

sample data has a copy paste issue and 754-Soap is not a concatenated value. Otherwise we do have another requirement

5

@ppr
Actually, it will be exactly like this so I need to concatenate both strings of Excel A and compare them with Excel B’s value

Regards
Sudharsan

6

@ppr

In the query you given
Dt1 → Excel A
Dt2 → Excel B right?

I have modified your query like this

(From d In BuildDataMatchingDT.AsEnumerable
Let cASet = {“furnizar”,“Segment”,“Piata”}
Let cBSet = {“FURNIZOR”,“SEGMENT”,“PIATA” }
Let chs = cASet.Select(Function (x1) d(x1)).toArray
Where Not Input_DTFromSource.AsEnumerable.Any(Function (d2) cBSet.Select(Function (e) d2(e)).toArray.SequenceEqual(chs))
Select r = d).CopyToDataTable

And it is returning the value of cases only but I want the output of cBSet and with all the columns (It will have up to 10-13 Columns)

If I interchange the dt in the query it throws an error like the column is not in datatable

Regards
Sudharsan

7

@ppr

It is not filtering the rows instead it takes all the rows from the datatable

Regards
Sudharsan

8

Thank you so much @ppr for sharing the query

I had got the answer by following these steps

  • After filtering the excel -A and got only the rows that have “No” in the “Status” column.
  • Build Datatable and created a new table with three columns
  • Loop through the filtered rows and add data row to add the rows as per requirements like concatenating two-row values into one and adding that value to the new column
  • With the following query I got the required datatable

Input_DTFromSource.AsEnumerable().Where(Function(row) Not BuildDataMatchingDT.AsEnumerable().Where(function(r) r("FURNIZOR").ToString.Equals(row("FURNIZOR").ToString) and r("SEGMENT").ToString.Equals(row("SEGMENT").ToString) and r("PIATA").ToString.Equals(row("PIATA").ToString)).Any).ToArray

Regards
Sudharsan

9

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