How to remove Specific rows and keep other rows in excel?

Help
1

Hello

I am trying to get remove the (with respect to Column "F: column name is “Account”) rows containing values starting with “1” except “161905” and I want to remove the null value rows also.

image

Thank You in Advance.

2

Hi @AryanSingh

Use row(“F”).ToString.except(“161905”)

use remove data row activity with row(“F”).ToString.except(“161905”)

Try this

Thanks
Ashwin S

3

@AshwinS2

I want to remove the values starting with “1” except “161905”. there are other values also like starting “8” and “4” etc.

So, Please guide me regarding this!!

4

Hy @AryanSingh,

You can try this in a for each row activity:

IF : (row(“Account”).ToString.StartsWith(“1”) And row(“Account”).ToString.Trim <> (“161905”)) Or row(“Account”).ToString.Trim.Equals(“”)

THEN delete row

5

@Dragos_Padurariu1

Ok…Thanks Let me try. Will get back to you.

Thank You

7

Hi @AryanSingh,

Try below steps, hope this helps you.

  1. Read excel into datatable [ReadRange Activity]
  2. Filter data table using below conditions [Filter Datatable Activity]
    a. Select “Keep”
    b. Column 5 is not null
    c. Column 5 = 161905
    d. Column 5 “Does not Starts with” 1
  3. Save the filtered data table into Excel.

Please try this, if this doesn’t works we can look for other options.
Other options are store separately the datarow value of 161905 and after filtering we need to add back to data table using add data row activity.

1 Like
8

@Dragos_Padurariu1

I am getting this error when i am using remove data row in the if activity then part

image
image697×379 51.5 KB

Please check.

9

@AryanSingh
This is wright, i did not take into account that when you try to delete rows when enumeration in a for each loop, you will get that error.

you can use @adheedhan solution. It is easier.

10

@AryanSingh

I hope this is your requirement …

Use below code in assign activity

tes.AsEnumerable().Where(Function(x) not String.IsNullOrWhiteSpace(x(“Account”).ToString) and (not x(“Account”).ToString.trim.StartsWith(“1”) or x(“Account”).ToString.trim.StartsWith(“161905”) ) and not x(“Account”).ToString.trim.StartsWith(“4”) and not x(“Account”).ToString.trim.StartsWith(“8”) ).CopyToDataTable

as well I attached xaml as well
Main.xaml (8.5 KB)

Check and let me know

1 Like
11

@Dragos_Padurariu1

Ok…i am trying that one. Thanks

12

@amaresan

ok. Will try @adheedhan method if doesn’t work. Will try your method.

Thank you

13

@adheedhan

Hello thanx for the steps, but when i am trying it this way then it is returning an empty table

image

I tried the value in double quotes also but not able to get any value.

Other filters are working fine. only this one giving empty table.

14

@adheedhan

Please guide me regarding this. Now only need this filter to work.

15

Please use the column index. Column index starts from 0.

16

@adheedhan

Still not working. Please check the image.

image

17

@AryanSingh
Is the column 5 is converted into Int32?
use below to convert a column to Int32.

You can do this by 2 approcah.

1 -
Building a data table with required columns and data type and storing the values in the created data table.
So while creating the datatable make the column to Int 32.

2 -
Use assign activity to convert the column data type.

image

dtClone.Columns(“DOB”).DataType = GetType(Int32)

So the filter would consider it as Int32 and filter it. By default the extracted data would be String.

18

@adheedhan

But the initial table has like 15-16 columns. Will have build all the things. Is there any way to change the value to int32 without building the table?

19

@AryanSingh
Use the assign activity which i mentioned earlier.
You can change the datatype of each column of a datatable.
“yourDatatable”.Columns(“columnName”).DataType = GetType(Int32)

20

@adheedhan

Ok…Thanks…Let me try.

21

@adheedhan

Tried this method getting this error.

image

Detailed error message while debugging

image