Powershell GetEnumerator on HashTable from CSV

Question

I would like to understand the best way to only print specific Hashable values coming in from a CSV.

The CSV is formatted like so:

Ticket,Last Updated,Priority,Assigned To,Email
2329,1-12-2017,Medium,Assignee1,assignee1@domain.tld
2417,5-23-2016,Low,Assignee1,assignee1@domain.tld
2416,4-20-2017,Medium,Assignee2,assignee2@domain.tld
2463,8-9-2016,Medium,Assignee2,assignee2@domain.tld
2481,3-4-2017,Low,Assignee3,assignee3@domain.tld
2499,2-22-2017,Medium,Assignee4,assignee4@domain.tld
2500,12-20-2016,High,Assignee5,assignee5@domain.tld
2498,11-30-2016,Medium,Assignee5,assignee5@domain.tld

In a perfect world the assumption is that I would not know the CSV headers (and thus not the nested hashtable values). I will be looping through to create the hashtable as the code below suggests:

$import = Import-Csv -Path .\import.csv|
    Group-Object -Property Email -AsHashTable
foreach ($i in $import.GetEnumerator()){
    $html = $($i.Value) | ConvertTo-Html -Head $htmlformat -Body $bodyformat
    $html | Write-Host
}

This works fine. The problem is that I would like to exclude the Email values from the finished result.

So I figured that, while not ideal, I know what the column headers of this CSV are always going to be, so will this work?:

$import = Import-Csv -Path .\import.csv|
    Group-Object -Property Email -AsHashTable
foreach ($i in $import.GetEnumerator()){
    $html = $($i.Value.'Ticket') + $($i.Value.'Last Updated') + $($i.Value.'Priority') + $($i.Value.'Assigned To') 
    $html | Write-Host
}

You can see the above code still uses Group-Object against the Email property but is excluded from the output. However the second I add | ConvertTo-Html -Head $htmlformat -Body $bodyformat all my data gets turned to numbers representing the character count of the string in the value. Even after reading a lot about the ConvertTo-Html issues with string values, being a simpleton in PowerShell, I am still stuck.

In summation:

1. It would be nice to get code that would exclude a Hashtable value (which differs from the current code that is including everything I want)
2. Understand, better, why the first example above can use ``ConvertTo-Html` without issue but the second will not.

Thanks.

Updated full script:

Which currently has an issue described in the comment to Conner below

$htmlformat  = '<title>Stale Tickets</title>'
$htmlformat += '<style type="text/css">'
$htmlformat += "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$htmlformat += "TH{border-width: 1px;padding: 7px;border-style: solid;border-color: black;background-color:thistle}"
$htmlformat += "TD{border-width: 1px;padding: 7px;border-style: solid;border-color: black;background-color:#ffffcc}"
$htmlformat += '</style>'
$bodyformat = '<h3></h3>'
$notice = "<font color='red'><b>This is an automated message.</b></font><br />"

$FromAddress    = "Spiceworks <noreply@domain.tld>"
$ToAddress      = "<My email address for now>"
$Subject        = "You have stale tickets!"
$SMTPserver     = "smtp.whatever.com"

if ((Get-Item "spiceworks_stale_open_ticket_alert.csv").length -gt 0kb)
    {
    $html = Import-Csv -Path .\spiceworks_stale_open_ticket_alert.csv|
    Group-Object -Property Email | ForEach-Object {
        $_.Group | Select-Object * -Exclude 'Email','Assigned To' | ConvertTo-Html -Head $htmlformat -Body $bodyformat
        $html = $html -replace "&lt;","<" -replace "&quot;",'"' -replace "&gt;",">"
        $body = $notice + $html
        Send-MailMessage -from $FromAddress -to $ToAddress -subject $Subject -bodyashtml ($body | Out-String) -priority High -smtpServer $SMTPserver        
        }

    }
else {exit;}

Final working iteration for anyone who stumbles across this:

if ((Get-Item "import.csv").length -gt 0kb)
    {
        Import-Csv -Path .\import.csv|
            Group-Object -Property Email|
            ForEach-Object {
                $Html = $_.Group | Select-Object * -Exclude 'Email','Assigned To' | ConvertTo-Html -Head $HtmlFormat -Body $BodyFormat
                $Html = $Html -replace "&lt;","<" -replace "&quot;",'"' -replace "&gt;",">"
                $Body = $notice + $Html + $notice2
                $Subject        = "Inactive Tickets for " + $_.Group[0].'Assigned To'
                $ToAddress = $_.Name
                Send-MailMessage -from $FromAddress -to $ToAddress -subject $Subject -BodyAsHtml ($Body | Out-String) -Priority High -SmtpServer $SMTPServer
            }   
    }
else {exit;}

Answer 1

I'm not sure if i'm quite understanding what you're trying to do here, if you're looking to Generate a HTML report of Tickets to send, then this should do it (excluding the Email part)

$HTML = Import-Csv ".\SpiceWorks_Tickets" | Select-Object * -ExcludeProperty 'Email' | ConvertTo-Html

Using Select-Object and the -ExcludeProperty param will let you leave out Email completely.

The ConvertTo-Html Object takes in a pretty limited set of objects which it knows how to convert, a PsCustomObject (which is what Import-Csv gives you) will work fine, however a Hashtable will not, so instead of outputting the Inner data, it will give you System.Collections.Hashtable+KeyCollection as that's all it knows how to get (I believe it just calls .ToString() on it and returns that)

If you'd like to loop through each member of the result of Group-Object and turn each Value into its own HTML Table then you can use this:

$ImportedCsv | Group-Object -Property Email | ForEach-Object {
    $_.Group | Select-Object * -Exclude 'Email' | ConvertTo-Html
}

edit in to send multiple emails:

$ImportedCsv | Group-Object -Property Email | ForEach-Object {
    $ToAddress = $_.Name
    $Body = $_.Group | Select-Object * -Exclude 'Email' | ConvertTo-Html -Head $HtmlFormat -Body $BodyFormat
    Send-MailMessage -From $FromAddress -To $ToAddress -Subject $Subject -BodyAsHtml "$Body" -Priority High -SmtpServer $SMTPServer
}

Answer 2

You could just remove the redundant property from your data:

foreach ($key in $import.keys) {
    foreach ($value in $import.$key) {
        $value.PSObject.Properties.Remove("Email")
    }
}