Need to remove the some mail id from the excel file

Help Activities
,
1

Hi all,

Excel file has few mail id’s, if the mail id contains aaa@gmail.com these mails needs to removed from the excel file.
Tried using if condition, it’s not working. Please guide me on this.

2

@lakshmi.mp

-Use the “Read Range” activity to read the data from the Excel file into a DataTable.

  • Use the “Filter DataTable” activity to filter rows based on the condition that the email address column should not contain “aaa@gmail.com.”
    -Use the “Write Range” activity to write the filtered DataTable back to the Excel file.
3

Hi @lakshmi.mp

    Read Range: ExcelSheet -> DataTable

filteredDataTable = dataTable.Select("EmailColumn <> 'aaa@gmail.com'").CopyToDataTable()

Write Range: filteredDataTable -> ExcelSheet

Hope it helps!!

4

@lakshmi.mp

yourDataTable.Rows.Remove(yourDataTable.Select("Email = 'aaa@gmail.com'")(0))

cheers…!

5

@Dilli_Reddy , mail id is not fixed it will be changing. Domain name is fixed.
@gmail.com is fixed

If any mail id’s ends with

@gmail.com

this has to be removed from the excel.
In this case what to do…

6

Hi @lakshmi.mp

Try this:

Dim filteredRows = dt.AsEnumerable().Where(Function(row) Not row.Field(Of String)("EmailColumn").EndsWith("@gmail.com")).CopyToDataTable()

→ Use Write Range Workbook to write filteredRows.

filteredRows is of DataType System.Data.DataTablr.

Hope it helps!!

7

@lakshmi.mp

can you provide which mail ids you need to remove like on what basis , is there any common values in that column

8 
dtExcelData = (From row In dtExcelData.AsEnumerable()
               Where Not row.Field(Of String)("EmailAddress").Equals("aaa@gmail.com")
               Select row).CopyToDataTable()
9

@Shiva_Nikhil
Mail id’s needs to be removed

Example:
acv@gmail.com
abc@gmail.com
xyrt@gmail.com
sdfg@gmail.com

Please go through these mail id’s

10

@lakshmi.mp

try this:

Regards

11

Hi you can use below steps:

  1. Use the “Read Range” activity to read the Excel file and store the data in a DataTable variable.

  2. Use the “For Each Row in Datatable” activity to loop through each row in the DataTable.

  3. Inside the loop, add an “If” activity:

    • Condition: row("Column_Name").ToString <> "aaa@gmail.com"

  4. Inside the “If” activity, use the “Remove Data Row” activity to remove the current row if the condition is not met.

  5. After the loop, use the “Write Range” activity to write the modified DataTable back to the Excel file.

or you can use below query

YourDataTable = YourDataTable.AsEnumerable().Where(Function(row) Not row.Field(Of String)(“Column_Name”).Equals(“aaa@gmail.com”)).CopyToDataTable()

12

@lakshmi.mp

can you try this once

dt.AsEnumerable.Where(Function(a) Not a("EmailColumn").tostring.EndsWith("@gmail.com")).CopyToDataTable
1 Like
13

@Shiva_Nikhil , able to remove the mail id’s as expected.
Thanks for helping…

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.