Delete Entier row met condition

[MD_Farhan1 (MD Farhan)]

i have excel in table format not range. now i want check one column (Serial No.) is empty delete entier row in table. datatable name profitTable

[vrdabberu (Varunraj Dabberu)]

Hi @MD_Farhan1

Try this query:

profitTable= profitTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(row.Field(Of String)("Serial No."))).CopyToDataTable

Regards

[MD_Farhan1 (MD Farhan)]

above code is not working but no error is showing.

[postwick (Paul Ostwick)]

He’s showing you how to delete a row from a datatable. However, it’s simpler just to use the Filter Data Table activity. Read the data into a datatable first then use Filter Data Table to get rid of what you want, then write back to Excel.

[mkankatala (Mahesh Kankatala)]

Hi @MD_Farhan1

Try the below linq expression in assign activity -

- Assign -> dt = (From row In dt.AsEnumerable
                  Where Not String.IsNullOrEmpty(row("Column name").toString)
                  Select row).copytodatatable()

Note - Change the name of the column within the double quotes in the LinQ Expression.

It’s working for me I have shared input and output images below :
Input -

Output -

Hope it helps!!

[vrdabberu (Varunraj Dabberu)]

Hi @MD_Farhan1

Sample Input:

Syntax:

profitTable= profitTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(Convert.ToString(row.Field(Of Object)("Serial No.")))).CopyToDataTable

Output:

Workflow:

xaml:
Sequence19.xaml (8.0 KB)

Use the above query this should work.

Regards

[lrtetala (Lakshman Reddy)]

Hi @MD_Farhan1

How about the following?

Input:

Output:

Regards,

[Archana_Gulli]

Hey @MD_Farhan1

->Use the “Read Range” activity to read the Excel table into a DataTable variable, i.e profitTable(Variable of datatable type). Specify the range or table name to read the data properly.

profitTable = profitTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(row.Field(Of String)(“Serial No.”))).CopyToDataTable()

[Nawazish_Ahmad]

Hi @MD_Farhan1

1. Read Excel file using Read Range Activity and store in variable profitTable as DataTable.
2. Take Assign activity and
   Use below expression:

profitTable = profitTable.AsEnumerable().Where(Function(row) Not String.IsNullOrEmpty(Convert.ToString(row.Field(Of Object)(“Serial No.”)))).CopyToDataTable

Hope it will helps you
Cheers!!