Need to extract Email address from the excel

Help Activities
, ,
1

Hi Team

Based on filtering the Customer Number in the Excel file we will get the final datatable.

Example

Test.xlsx (9.6 KB)

Above is the filtered datatable based on Customer Number.

Need to extract the email address belongs to that customer number into a variable seperated with “;”

Note:
After filtereing excel based on Customer Number the output may be 1 row, 2 rows , 3 rows etc

from all the rows need to extract emailid and add it to single variable seperated with “;” and if we found duplicates then need to remove them

Thanks
Likitha

2

Hi @vinjam_likitha

Once try with this steps

  1. Read Range (Excel file → DataTable)
  2. Initialize dictionary: customerEmails (Dictionary<String, List>)
  3. Initialize dictionary: uniqueEmails (Dictionary<String, String>)
  4. For Each Row (DataTable)
    a. Assign email (String) = row(“EmailColumnName”).ToString() // Replace with your email column name
    b. Assign customerNumber (String) = row(“CustomerNumberColumnName”).ToString() // Replace with your customer number column name
    c. If customerEmails.ContainsKey(customerNumber)
    • Append email to the list: customerEmails(customerNumber).Add(email)
      d. Else
    • Add customerNumber to the dictionary with a new list: customerEmails.Add(customerNumber, New List(Of String) From {email})
  5. For Each item in customerEmails
    a. Assign uniqueEmails(item.Key) (String) = String.Join(“;”, item.Value.Distinct())
3

@vinjam_likitha

use read range for reading the excel

stremails=string.join(“,”,dt1.AsEnumerable.Select(Function(x) x(0).ToString.trim).ToArray.distinct)

stremails is the string datatype

Hope this helps

1 Like
4

Hi @vinjam_likitha ,

Could also check on the below, Maybe more specific to your data :

String.Join(";",DT.AsEnumerable.Select(Function(x)x("EMAIL_ADDRESS").ToString).Distinct.ToArray)

Here, DT is assumed to the filtered Datatable variable.