Filter datatable by blanks

[Vashisht (Vashisht Devasani)]

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.

[NiranjanKN]

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

Thanks

[Vashisht (Vashisht Devasani)]

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

[NiranjanKN]

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

[samir (Samir Mohite)]

@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

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

I hope this’ll help you.

[Vashisht (Vashisht Devasani)]

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.

[NiranjanKN]

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

Thanks

[samir (Samir Mohite)]

@samir

for the table name, first you have to create table in excel and pass that table name in activity parameter

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

[Vashisht (Vashisht Devasani)]

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.

[NiranjanKN]

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

[samir (Samir Mohite)]

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

[NiranjanKN]

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.

[samir (Samir Mohite)]

hey @NiranjanKN,

@NiranjanKN

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.

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

[NiranjanKN]

Sure, @samir

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

Thanks

[NiranjanKN]

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

Thanks

[lakshman (Ganta lakshman)]

@NiranjanKN

1. First read the data from input file and assign it to one DataTable variable and say ‘yourDT’.

2. And then use below expression to filter DataTable based on blank columns in that DataTable.

        filterDT = yourDT.AsEnumerable().Where(Function(row) row("MSTA").Tostring.Trim = ''").CopyToDataTable
   

[samir (Samir Mohite)]

hey @NiranjanKN,

I’m trying, how to use Not Equal to in filter options of filter table activity.
But there’s an alternate option, which is recording a macro in excel and use it by using Invoke VBA activity
here’s the Example code,

Sub Macro1()

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=5, Criteria1:= _
        "<>A2", Operator:=xlAnd
End Sub

This worked well.
but, as you can see I’ve used “<>A2” but while using <> in filter option it’s giving error.

So using recording macro by clicking on,

1. column filter.
2. text filters.
3. Does Not Equal to
   and then give value as A2, click OK.

So this could be alternate option.

[Luis261980 (Luis Nunes)]

@ NiranjanKN Hi,

Ex from your excel → row(2).toString = YourString
or u can use like
IF
String.isNullorEmpty(YourString)
Then → Will pass
Else → Will perform what you whant

GL with your project

[ChetanB (Chetan Bhandare)]

Thank you.