CODEWITHSUNDEEP
powershell
jeff
jeff

Reputation: 101

Grouping and emailing CSV Data with Powershell

I have a CSV file that lists employees email address and some random information per transaction that I want to email them individually. Here is the current format:

| Email        | risk_level | breach_title | breach_desc | breach_date
| ------------ | -----------| -------------| ------------|------------
| [email protected] | High       | Hack 1       | Bad things  | 1/1/2021   
| [email protected] | Medium     | Hack 2       | Not so bad  | 1/2/2021
| [email protected] | High       | Hack 1       | Bad things  | 1/1/2021

[email protected] would get an email with a body of:

risk_level: High
breach_title: Hack 1
breach_desc: bad things
breach_date: 1/1/2021

risk_level: Medium
breach_title: Hack 2
breach_description: not so bad
breach_date: 1/2/2021

[email protected] would get an email with a body of:

risk_level: High
breach_title: Hack 1
breach_description: bad things
breach_date: 1/1/2021

Here is what I have tried but ended up back at same result set

$csv = Import-csv -Path C:\Users\jeffr\Downloads\eecpro_results.csv | Group-Object email

# Create a bucket
$email;
$breach_data = @()

$breaches = ForEach($u in $csv) {

    # Store the breach data
    $bd = $u.Group
    
    foreach ($b in $bd){
        $row = New-Object PSObject
        $row | Add-Member -MemberType NoteProperty -Name "Email" -Value $u.Name
        $row | Add-Member -MemberType NoteProperty -Name "Risk Level" -Value $b.risk_level
        $row | Add-Member -MemberType NoteProperty -Name "Breach Title" -Value $b.breach_title
        $row | Add-Member -MemberType NoteProperty -Name "Breach Description" -Value $b.breach_description
        $row | Add-Member -MemberType NoteProperty -Name "Breach Classification" -Value $b.breach_data_class
        $breach_data += $row
    }

}

# Export to CSV
$data | Export-Csv C:\Rec\test.csv -NoTypeInformation

The goal would be some sort of email merge that groups all records by email address.

Upvotes: 1

Views: 154

Answers (1)

Cpt.Whale
Cpt.Whale

Reputation: 5321

You want to group by email address before iterating through your list. For example:

# Group the users first:
$Grouped = $csv | Group email

$Emails = Foreach ($user in $Grouped) {
 
  # Add "Private: " to the beginning of each entry
  $PrivateData = $user.Group.'Breach Description' | %{"Private: $_"}

  # Make a multi-line string out of the list of descriptions
  $user | select Name,
    @{l='Data';e={($PrivateData | Format-List | Out-String | Sort).Trim()}}
    # Other fields etc...

}

# Example of sending the emails
$Emails | Send-MailMessage -From '[email protected]' -Subject 'Breach' -To $_.Name -Body $_.Data -SmtpServer mail.abc.com

The result emails are:

Subject : Breach
To      : [email protected]
Body    : Private: private data
          Private: some of jeffs different data

Subject : Breach
To      : [email protected]
Body    : Private: bills private message

Upvotes: 1

Related Questions