How to filter null rows from DT

Help Studio
, ,
1

Hi All,

I have excel i need to skip the rows which has null cell values. I have attached workflow in for each row using if condition I can able to filter is there any other possible way to do kindly suggest.
Data_Validation.xaml (10.2 KB)
Customer_Auto_Approval_With_Data.xlsx (11.0 KB)

Thanks,
Hima

2

Hey @thima ,

You can use below linq code in an assign activity
left side: autoAprroveDT
Right side : paste the below code

(From row In autoAprroveDT.AsEnumerable
Where Not row.ItemArray.Any(Function (x) If(isNothing(x), True, String.IsNullOrWhiteSpace(x.toString.trim)))
Select row
).CopyToDataTable

This will discard the rows if any one of the column is empty
So by looking at your excel , There are no rows which are filled in every column

Below is the xaml
New folder (3).zip (12.1 KB)

Hope it helps you out!

3

Hi @thima

Follow the below process steps
=> Use read range workbook activity, after this
=> use an assign activity and use the below linq query

- Assign -> OutputDatatable = InputDatatable.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

=> After assign activity use write range workbook activity to write the OutputDatatable to same excel file.

Or----------------------------------------------------

=> Use read range workbook activity to read the excel file and store it in a datatable variable let call the variable as InputDatatable.
=> After read range use the filter datatable activity, Click on the Configure filter option and give the columns names which you want to filter and check the remove option. Create a variable for the output of Filter datatable activity. Let call it as OutputDatatable.
=> Use write range workbook activity to write the OutputDatatable to same excel file.

Hope it helps!!

4

Hi @thima

Please find below xaml for your reference

BlankProcess8.zip (170.1 KB)

Regards,

5

@thima

Try this in assign activity …it will remove those rows

autoAprroveDT.AsEnumerable.Where(function(x) Not x.ItemArray.All(Function (x) Not isNothing(x) AndAlso Not String.IsNullOrEmpty(x.toString.trim))).CopyToDataTable

Cheers

6

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