How to get the wrong records?

Help Activities
, ,
1

Hi everyone!

I have 49k rows in my real excel but this excel is example for here.
I have to find wrong records in Fatura ID(“Export Exceli”) column which equals #N/A. Ater that, I should send wrong records to my manager.
How to get them to another excel sheet and send it?

image

2

@ozgecatak
Read the excel using Read range with headders

Then use Filter datatable to filter with your condition

image
image1070×507 37.8 KB

3

HI @ozgecatak

You can use filter datatable activity and pass the condition like
image

Give the column name as “Fatura ID(”“Export Exceli”“)” if failed use the index

after that write the filtered datatable in the new excel or sheet and send mail to your manager

Regards
Sudharsan

4

@ozgecatak ,

output datatable variable from the filter condition will have the filtered values , then to share it through email, you can save that data table as an excel file using “Write Range Activity” and use the saved file path as attachment for the “Sent Mail Activity”

5

I couldnt work. When I applied it, it gives me all datatable again.
When it read range excel, I checked the values. I find this:

image

6

@ozgecatak ,
Can you try giving “contains” instead of = sign?

7

It didnt work.

8

@ozgecatak ,
Can you show me your filter condition (Screenshot)?

9

image
image694×481 14.1 KB

10

image

This is my input excel, after read range

11

HI @ozgecatak

How about this LINQ expression?

(From d In DtRead.AsEnumerable
Let cols = New String(){"Fatura ID(Export Exceli)"}
Where cols.Any(Function (c) d(c).ToString.ToUpper.Trim.Equals(" #N/A"))
Select r = d).CopyToDataTable

Regards
Gokul

12

Alternative Method @ozgecatak

Dt.AsEnumerable.Where(Function(r) r("PRDDATE").ToString)="#N/A" ).CopyToDataTable

Regards
Gokul

13

I dont understand this Linq, can you explain it?

14

@ozgecatak ,
Can you just verify whether the value is coming as #NA in the input datatable while reading,

I guess it will come as empty filed , if it is empty you can change the condition as “Is empty”

image

15

It doesnt work. :frowning:

16

@ozgecatak ,

Can you print the datatable in a message box by converting it to a string using(“Output datatable activity”)
and share its screenshot?

17

HI @ozgecatak

Check Out this XAML file

FilterUsingLinq.xaml (7.4 KB)

image
image698×487 17.5 KB

Regards
Gokul

18

Do one thing @ozgecatak

Read your excel and use a for each row in datatable inside that use a log message and write the values of that column and check how the value is appearing from the excel after few rows stop the process and copy that #N/A value in the output panel and then use that in the value field of filter datatable

Regards
Sudharsan

19

It comes {}

20

Have you got your Output Ah ? @ozgecatak