How to remove rows from data table based on one column

Help Studio
, , , , , ,
1

Hello Team,

How can we remove rows from excel(data table) based on one column

Example this is the table
|Project|Division|Total Amt|
|ABX|NA|100|
|XXN|INDIA|INDIA_VX|
|YYN|AMERICA|AMERICA_NX|

So it should remove from column Total Amt
where value is INDIA_VX or AMERICA_NX

Have attached the file which contains the current data format
And the format that is required

Project data Current format and required format.xlsx (6.2 KB)

Thanks in advance

2

@NISHITHA

Try this steps

  1. Read Range activity to read the excel file and declare a datatable variable let dt_test
  2. Use Fitler Datatable and give your dt_test and outdatatable as dt_test
  3. in the column give “Total Amt” <> “INDIA_VX” and click + button and choose as OR and again column “Total Amt” <> “AMERICA_NX”
  4. Now use Write range activity and pass the datatable as dt_test

So this time you will see only the 100 value rows

Hope this may help you

Thanks,
Srini

3

Hii @Srini84

Thanks for your response.I tried the way you have suggested but its not working

Could you share the filter screen screenshot

Thanks

4

Hi @NISHITHA ,

I believe from the Expected format provided, you would require to filter rows which only has the numeric values in the Column Total Amt

For such case, we can use a Linq Expression like below using an Assign activity :

DT = DT.AsEnumerable.Where(Function(x)x("Total Amt").ToString.IsNumeric).CopyToDatatable

You could then write the Filtered Datatable to a new Excel sheet.

Let us know if this is not what you required.

5

@NISHITHA

Check below for your reference

image

Hope this may help you

Thanks,
Srini

6

Hi @NISHITHA

Below are the Steps you can apply for removing rows:

  1. Use “Read Range” activity to read the excel file and create a variable (DT_1) of type Datatable.
  2. Use “Filter Datatable” Activity and give your Input and Output Datatable as DT_1.
  3. Below You can find, like that you have to configure that Filter data table activity.
    image
  4. Now You can use “Write range” activity and pass the DT_1 as Datatable to write in the File.

Hope this works,
Best Regards.

7

Thanks @Srini84 and @pratik.pandya

8

Hi @supermanPunch ,

Yes that is what we wanted to achieve

Thanks this one worked:)

1 Like
9

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