How to delete row in excel file with certain keyword

dear all,
i have an excel files, as follows figure displayed. how could i achieve this function, delete the row AAA in this excel files? (we should keep attention, the position of row AAA is not fixed , it is a variable) anybody could help me ? thanks a lot in advance. !

Hi @chrisjiyiwei

In your case, I think the below component is helpful for you.

how to use this activities? could you give me a demo ? thanks a lot

@chrisjiyiwei Considering that the Code AAA only appears in the Code Column, this can be an alternate solution, you can try these following Steps :

  1. Read the Excel File using Read Range, Get Output as Datatable say DT.

  2. Use this Expression in an Assign Statement :
    DT = DT.AsEnumerable.Where(Function(x)Not(x(“Code”).ToString.ToLower.Equals(“aaa”))).CopyToDataTable

  3. Write the DT to a new File using Write Range Activity.

dear guy,

i mean not only delete cell AAA, it means all the row 8, i want to delete AAA, cc and 1, but the position of row may change, now is 8, sometimes it could be 7 or 6. how could i achieve this function?

@chrisjiyiwei Yes, It will delete the whole row whose Code Column contains “AAA”. Can you give a try and Check if that is what you needed ? :sweat_smile:

what is function(x), the x means?

Use Read Range —DT1
Then use Filter Datatable
In that use
Input DT1 and Output DT2
use Condition
Column Name in your case “Code” = “AAA”
DT2 will give u data without AAA rows
U can write it in other excel file or sheet
using write range
Dont write it in That sheet only…

@chrisjiyiwei It is a Lambda Expression which is used in Linq Statements, It helps us to Iterate over each object from the Collection of Objects and perform decisions based on operations.

i use this way, but the new excel file is empty.

Please chec below workflow
Filtertest.xlsx (9.1 KB) Filter.xaml (6.8 KB)

1 Like