Linq query to fetch rows from 10000+ rows in excel

Help Activities
, , ,
1

Hi ,
Please help me with solution.
I want to fetch row from excel with condition row has value E from thousands of row almost 10000+ rows.
once row is fetched i want that fetched row and previous row from fetched row.
normal loop takes time as there are more rows. so im looking for Linq kind of options.

Sample what im looking for:
Actual rows:

Run 3339991 Statement is passed
LuC 33 W
Run 485691 Statement is passed
Shan 24 S
Run 6739991 Statement is passed
wan 40 N
Run 78995 Statement is Error
Cor 33 E
Run 579991 Statement is passed
Mark 22 W
Run 78651 Statement is Error
Ken 31 E
Run 897423 Statement is passed
Luis 34 N
Run 659334 Statement is Error
Mathew 28 E

Row expected:

|Run|78995|Statement is Error|
|Cor|33|E|
|Run|78651|Statement is Error|
|Ken|31|E|
|Run|659334|Statement is Error|
|Mathew|28|E|

Thanks

2

Hi,

Can you try the following sample?

image
image859×637 31.6 KB

arrIndex= dt.AsEnumerable.Where(Function(r) r(2).ToString="E").Select(Function(r) dt.Rows.IndexOf(r)).ToArray()

Then

dt = dt.AsEnumerable.Where(Function(r,i) arrIndex.Contains(i) OrElse arrIndex.Contains(i+1)).CopyToDataTable()

Sample20230922-7L.zip (2.9 KB)

Regards,

3

Thanks for your response. This works like charm.

But my requirement is now little bit modified. i want to fetch those rows and put it in same excel under the row where the row data contains" Exec" and delete from original position to avoid duplicate data and also i want to update in excel wherever E is there need to mention as Error S means Success and N Means not applicable.
is this possible thru linq?

4

Hi,

Can you share input sample and expected output?

Regards,

5

Attached sample input file in that from row 86 to 90 i have error data and i need to cut from there and bring to top which is the row contains the word “Exec. Req”. But in this sheet it will occur in multiple places i want error data under first occurence here under row 30. Also data are in single cell i want to split it into columns. All operations should happen in same sheet1.
Input data.xlsx (10.4 KB)

Let me know if it is clear and what is possible in this.
Thanks.

6

Hi,

If possible, can you also share expected output as file?

Regards,

7

Hi , Attached sample
FormatExcelData.xlsx (11.2 KB)
file with input and expected output as separate sheet but i want expected output in same sheet