Filter datatable by blanks

Help
, ,
1

Hey guys,

I need to filter this MRA.xlsx (9.4 KB) file, with the MSTA column containing only blanks.

I tried filter datatable activity, by using IsEmpty option, but the column contains space.
So, please let me know how this can be resolved.

Thank you.

2

Fine we can try with Select method
Like this
Yourdatatablename = Yourdatatablename.Select(“[columnname1] <> ‘ ‘ OR [columnname1] <> ‘’ “).CopyToDatatable()

Cheers @NiranjanKN

3

@NiranjanKN

Try this:

    yourDT.AsEnumerable().Where(Function(row) row("MSTA").Tostring.Trim = ''").ToArray()
4

Hi @NiranjanKN,

Please check the below Zip file, i developed according to your requirement along with the excel you mentioned in simple terms…

forum.zip (25.0 KB)

Any doubts let me know.Otherwise mark as solution and close the thread.

Cheers.
Vashisht.

5

Hey, @Vashisht
Thanks for the code.
One doubt is that, can’t we write the filtered data in the same sheet ?

Thank you

6

Yes, you can write but if the second time writing data is less than already present data it will not over write that…
Solution:

1.Use Bigger range like “A1: J100” in the write range then it will work good.

Let me know if you achieved it…
Cheers.
Vashisht.

7

Hey @Vashisht
Can’t we use just “” in the range, because we dunno the range.

Thanks

1 Like
8

But if we keep empty range it will not overwrite the whole sheet.

9

So, is there any alternate like using rows.count +1 or anything else ?

1 Like
10

@NiranjanKN,

If you want to filter excel sheet, I’ll suggest you to use Filter Table activity

  1. take Filter Table activity Excel Application Scope and set the workbook path.
  2. Set all the parameters like sheetName, ColumnName and table name. as shown in below shot.
    for the table name, first you have to create table in excel and pass that table name in activity parameter

image

  1. Set the FilterOptions —> {“”}
    So it’ll filter table on rows whose values are blanks of specified column.

I hope this’ll help you.

11

If the number of columns is fixed then You can get the range of the data table…

1.Like the column is known and the rows count can be derived by DT.rows.count
2.Then you can give the range as “A”+DT.rows.count.tostring+“ColumnCountAlphabet”+DT.rows.count.tostring

Then it will work fine…

Cheers.
Vashisht.

12

Hey, @samir
I tried to use this, but didn’t get where to get the table name ?

Thanks

13

@NiranjanKN
You need to create table in excel, click on any cell in table and press ctrl+t

14

Hi @NiranjanKN,

I have work around solution for this problem.

1.In my workflow just add one activity i.e Balareva Delete sheet activity and mention path of the excel and the sheet name…

2.Then use the write range with blank only it will work fine.

3.The only problem is the excel sheet should contain some other sheet also…

4.You may also use rename activities to achieve this…

Cheers.
Vashisht.

15

Hey, @samir
What can be mentioned in the Filter options, if I need to keep only rows which as Hello in the ‘MSTA’ column.

Thanks

16

Then set the FilterOptions —> {“Hello”} @NiranjanKN

17

Hey, @samir
There’ll be multiple data in it,
For example, in Column1 - there’ll be blank cells, cells with A1, A2, A3 data.
I need to delete all the rows with cell containing only A2 data, but keep all rows with cell containing blank,A1 and A3 data.

Thank you.

18

hey @NiranjanKN,

I don’t get what you’re saying, could you please elaborate more.

19

Sure, @samir

Screenshot%20(5)
Screenshot%20(5)810×456 22.7 KB

In this file, I need to filter out MSTA column containing A2 data and keep all other rows.

Thanks

20

I tried and got it FilterBlank.zip (9.9 KB)

Thanks

1 Like