Filter data and remove the rows

Help Studio
,
1

Hello

One Excel sheet some set of data’s are there like (dhiebdj-FS,hdjebfjsbsj-FS,hdidhdudh-RE,hdudhdjsh-RE,hdudhdu,dhdudhu-R,hdudhfjej-F). we want to remove the whole rows are endwith (-FS,-RE)
In the sheet 3 column is there In A column has (dhiebdj-FS,hdjebfjsbsj-FS,hdidhdudh-RE,hdudhdjsh-RE,hdudhdu,dhdudhu-R,hdudhfjej-F)
B column has ( fshdh,heidv,bdjdb,bdjeb,dbhrhd,rbdjdj,dbjdhd,)
C column has ( se,bdjd,ndej,ee,rhrj,bejd,dr,df)

2

HI @Aarthy1

Can you share the excel files with input and output?

Regards
Sudharsan

3

Hi @Aarthy1 ,

Can you try this

InputDT

image

OutputDT = inputDT.AsEnumerable.Where(Function(DR) Not string.Join(“,”,DR.ItemArray.ToArray).Contains(“-FS”) AND NOT string.Join(“,”,DR.ItemArray.ToArray).Contains(“-RE”)).CopyToDataTable()

Your output will be

image

Thanks!
FilterDataAndRemove.xaml (7.4 KB)

4

But , in orginal Excel i have many columns upto BT

5

This is the file , please check and inform me the correct answer
In the Excel file i mension the requirement also

file.xlsx (10 KB)

6

Hi @Aarthy1
Read the excel and store it in datatable and inside for each row in datatable use if condition currentrow (0). tostring.endswith(“-FS”) or currentrow(0).tostring.endswith(“-RE”) then use remove datarow activity.after wise write range activity to write the filtered datatable
Or
Use filter datatable activity and give the if condition inside and try

7

I tried but i facing the error

IMG_20221218_190320_964.jpg
IMG_20221218_190320_964.jpg2080×1560 1000 KB

IMG_20221218_190342_826.jpg
IMG_20221218_190342_826.jpg2080×1560 1.02 MB

8

@Aarthy1

You want to check only one column or all the columns?

If only one column then use filter datatabe

If multiple columns…then follow the steps

  1. Read data in datatable dt.
  2. For each (not for each row in datatable)… give ‘in’ argument as Enumerable.Range(0,dt.Columns.Count-1).ToArray and change ‘type’ argument in properties to int32
  3. Inside loop use filter datatable with input and output datatable both as dt and in filter condition give currentitem endswith "your string" and select the mode as remove or select does not end with and select mode as keep anything is same

Hope this helps

Cheers

9

While using filter data table the formula in the table is gone

10

Clone the datatable and use add datarow activity to add the row in cloned datatable in else block

11

@Aarthy1

You have formulae in excel? If yea then use for each row in excel and then check each row in if condition and use delete row…

Cheers