Filter rows In Datatable


#1

Hello friends,
20180703.xlsx (17.1 KB)

I need to execute a Bot so that it takes only the rows which haven’t got a string in the column “Esito Pre Disdetta”.
I have to skip the rows containing the value in this column and process only the remaining rows.
How is it possible?
Camilla :slight_smile:


#2

I would suggest a For Each Activity:

For the list of items, do it like that:
Foreach row in yourDataTable.Select("[Esito Pre Disdetta]<>''")

This will iterate through all the rows that have a nonempty string in that column.

The output of the .Select is an array of DataRows, this is why you need to use the For Each with custom TypeArgument of DataRow:
image


#3

Thank you so much,
I’ll try.
But as my workflow is organised in a way in which it should recognise the current index of row,
is it possible to get the index of the first row with “[Esito Pre Disdetta]=’’”?
Thank you so much,
Camilla :slight_smile:


#4

Sure. The fact that .Select returns the array of rows helps here.

For each row in your iteration, you can use this to get the index of it in the original DataTable:

yourDataTable.Rows.IndexOf(row)

This should return the index of the specific row as it is set in the original, non-filtered DataTable.


#5

Hi @CamiCat,

Here I have attached the file . May be a fix.

File : SkipRows.zip (16.0 KB)

Regards
Balamurugan.S


#6

Thank you so much @balupad14.
How is it possible to check if a row belong to a DataRow[]?
Thank you so much,
Camilla :slight_smile:


#7

Hi @CamiCat,

Do you need to know that it belongs which one . Total rows (dtExcel) or filtered rows (dtResult) ?

Regards
Balamurugan.S


#8

Filtered Rows?
Thank you so much :slight_smile:
Regards,
Camilla


#9

Hi @CamiCat

When you do the for loop with dtResult , it always the row belongs to the filtered data.

Here I have changed the source.

File : SkipRows.zip (16.3 KB)

Regards
Balamurugan


#10

Thank you so much @balupad14.
I get the following error in case of Excel alreary completed with all the columns full.
It should not find any row.


How can I solve it?
Thank you,
Camilla :slight_smile:


#11

Hi @CamiCat

See attachment for easy solution with error handling for situations when there are no empty rows :slight_smile:
ProcessNonEmptyRows.zip (15.7 KB)


#12

@CamiCat

Let us take dta as the DataTable obtained by reading your Excel Sheet.
drABC is the DataRow Array which will contain only rows where column"Esito Pre Disdetta" value is Empty.

drABC = (From p In dta.Select
                             where string.IsNullOrEmpty(p("Esito Pre Disdetta").ToString)
                            Select p).TOArray

If drABC.Count>0
dtABC=drABC.CopyToDataTable

Regards,
Mahesh


#13

HI @CamiCat,

I have changed the source. you can check it now.

File :SkipRows2.zip (16.6 KB)

Regards
Balamurugan.S


#14

Thank you so much @MAHESH1.
Is it possible to filter the datatable so that it selects the row with the colums “Esito Pre Disdetta” set to a null value(already done) or to “data non presente nelle email” ?
Maybe with an Or option.
Thank you so much?
Camilla :slight_smile:


#15

@CamiCat

Yes you can,

drABC =         (From p In dta.Select
                        where string.IsNullOrEmpty(p("Esito Pre Disdetta").ToString) or p("Esito Pre Disdetta").ToString.Equals(" “data non presente nelle email”)
                        Select p).TOArray

Regards,
Mahesh