Extracting Tables from .CSV for Use in Email

Question

I have a .csv file that contains well over 1000 entries of former employees of the company I work for, and I am attempting to split it up by employee into a format that I can insert into an email template. To exacerbate things, many (but not all) of the employees have more than one entry, and the .csv contains a considerable amount of information that is completely unnecessary for the people I will be sending the emails to. Ideally I would like to exclude these columns, but I am having quite a bit of difficulty with it. It seems to me that format-table will be the cmdlet I will be needing, but I am not 100% certain. Thus far I have tried:

import-csv C:\filepath\xx.csv | format-table -groupby (key value)

which does return the information organized in the way I would like, however I do not know how to split the information from there so I can only send the information for the specific employee. This also includes quite a bit of extraneous information, as previously mentioned. I have also tried:

import-csv C:\filepath\xx.csv | format-table -groupby (key value) -property (properties I want,separated by commas)

However, this is still returning the same extraneous information. I also tried using a foreach loop to iterate through the .csv, store the information I want in variables, then store those variables in an array, then pipe that array into format-table, which results in a mess. I have also tried putting the .csv into a hashtable grouped by the key value I want, however that results in the rest of the information being put into a string. I believe I should be able to split this string using regex, however I am not at all familiar with regex and have no idea where to start with that. I'm pretty well stumped at this point. Any help would be greatly appreciated.

EDIT

After following @4c74356b41's suggestion (for which I am very grateful), I am able to output a list of tables with only the pertinent information. However, I am still unable to split this list of tables into the individual tables I need. I currently have a .txt file which I am using as a template, which I would like to add the table for the individual users to. I have so far had success with using get-content to retrieve the contents of the .txt, -replace to replace several other fields on the template from information in the original .csv (eg name, manager, etc), and then out-file to store the edited template into a temp file, which is then saved as a draft to outlook. I attempted to add another foreach loop inside the previous foreach loop to add the tables into the template, but that is returning templates with the line 'Microsoft.PowerShell.Commands.Internal.Format.FormatStartData' in place of the table. It is also creating a draft for each entry rather than just one for each unique user id. Here is all the code I have so far:

$olFolderDrafts = 16
$ol = New-Object -comObject Outlook.Application 
$ns = $ol.GetNameSpace("MAPI")
$file= import-csv H:\filepath\term_rep.csv
$data= import-csv H:\filepath\term_rep.csv | select-object "Inactive Emp Name","User ID","Loc","Equipment Descr","Tag Number","Serial Number"
$table= $data | Format-Table -GroupBy "User ID"
foreach($i in $file)
    {$user = $i | select-object -expandproperty "Inactive Emp Name"
    $uid= $i | select-object -expandproperty "User ID"
    $manager = $i | Select-Object -expandproperty "Manager"
    $loc= $i | select -ExpandProperty "Loc"
    $tagnum= $i | select -ExpandProperty "Tag Number"
    $sernum= $i | select -ExpandProperty "Serial Number"
    $path="C:\filepath\term_email.txt"
    $newpath = [system.io.path]::GetTempFileName() 
    (Get-content $path) -replace "USER_NAME",$user `
    -replace "USER_ID",$uid | out-file $newpath
    foreach($id in $table){
        (Get-Content $newpath) -replace "EQUIPMENT_LIST", $id | out-file $newpath
    }
    $subject="Equipment for user $uid"
    $body= get-content $newpath
    $mail = $ol.CreateItem(0)
    $mail.To = $manager 
    $mail.CC = $null
    $mail.Subject = $subject  
    $mail.Body = $body -join "`n"
    $mail.save()}

Answer 1

To reduce noise and duplicates I would find a column that is specific to a user, such as UserName, or Email. Then group by that, and select the first item in each group. Then you could pipe to Select to reduce noise from extraneous columns. Something like:

import-csv C:\filepath\xx.csv | Group UserName | ForEach{$_.Group[0]} | Select FirstName,LastName,Email,UserName,SeparationDate 

Then you can do with it what you want... Pipe to another ForEach loop to work with each record one at a time, or pipe it to Export-CSV to generate a new CSV with reduced clutter that you could work with. You could even have PowerShell create the emails for you, depending on how fancy you want to get.

If you need further help please update your question with an example of what your desired output to be, or what exactly you are trying to accomplish with each entry.

Edit: Ok, so when it comes down to it you want a insert a table of things for any records matching a specific user into an email, so that you can get their stuff back from their manager after they're separated from the company. Cool, we can do that. To start off with, you're using the Outlook ComObject, and generating your email that way. Awesome, I've done it myself, and you can do some great stuff that way! I highly recommend using HTML here. It makes the email look more professional, and lets us insert a nice looking table into the email instead of some formatted text that will look funny due to spacing once its in the email.

So, let's back up from the issue just a hair, and redo your email template. Pop open MS Word and open your template. Now, go to File and Save As. Select 'Web Page, Filtered' as your document type, and name it the same thing you already had.

So, rather than loading the list twice, then exporting, and blah, blah, blah, we're going to shorten things a little. We load your CSV once. For that matter, we don't need to read the body of the email once for each user, so let's just load it up one time here before the loop as well. Then we pipe the User ID field to Select-Object using the -Unique switch, so that we only get one email generated per user. So, the script up to there looks like this:

$olFolderDrafts = 16
$ol = New-Object -comObject Outlook.Application 
$ns = $ol.GetNameSpace("MAPI")
$file= import-csv H:\filepath\term_rep.csv
$path="C:\filepath\term_email.txt"
$body = (Get-content $path) -join "`n"
foreach($i in ($file.'User ID'|Select -Unique))
    {

Now, there's no need to assign all of those variables like you were, so I'm skipping that part. So we'll find the first instance of the current user, and save that to a variable.

    $User = $File|Where{$_.'User ID' -eq $i}|Select -First 1

Once we've got that we will find all of their records in the list, select only the properties that you are interested in, and convert the output to an HTML table using ConvertTo-HTML.

    $Equip = $File|Where{$_.'User ID' -eq $i}|select-object "Inactive Emp Name","User ID","Loc","Equipment Descr","Tag Number","Serial Number"|ConvertTo-Html -Property '*' -As Table -Fragment

Now that is a pretty long line, but most of it is just selecting the right properties. The magic happens in the last bit where we tell it to convert the data to a HTML. Specifically we want everything (specified by -Property '*'), that we want it to be converted as a table, and that this is just a fragment, so it doesn't try to put all the preceding and following tags in there (so no <HTML> and </HTML> type tags since it's being injected into the middle of existing HTML.)

Then we do the replaces, make the email, assign the properties, yada, yada, yada, you pretty much already had all this...

    $HTMLbody = $body -replace "USER_NAME",$user.'Inactive Emp Name' -replace "USER_ID",$user.'User ID' -replace "EQUIPMENT_LIST", $Equip
    $subject="Equipment for user " + $User.'User ID'
    $mail = $ol.CreateItem(0)
    $mail.To = $User.Manager 
    $mail.Subject = $subject  
    $mail.HTMLBody = $HTMLbody
    $mail.save()
    }

Save it, and done! Loop to the next user, and repeat. So put that together and you get:

$olFolderDrafts = 16
$ol = New-Object -comObject Outlook.Application 
$ns = $ol.GetNameSpace("MAPI")
$file= import-csv H:\filepath\term_rep.csv
$path="C:\temp\email.htm"
$body = (Get-content $path) -join "`n"
foreach($i in ($file.'User ID'|Select -Unique))
    {
    $User = $File|Where{$_.'User ID' -eq $i}|Select -First 1
    $Equip = $File|Where{$_.'User ID' -eq $i}|select-object "Inactive Emp Name","User ID","Loc","Equipment Descr","Tag Number","Serial Number"|ConvertTo-Html -Property '*' -As Table -Fragment
    $HTMLbody = $body -replace "USER_NAME",$user.'Inactive Emp Name' -replace "USER_ID",$user.'User ID' -replace "EQUIPMENT_LIST", $Equip
    $subject="Equipment for user " + $User.'User ID'
    $mail = $ol.CreateItem(0)
    $mail.To = $User.Manager 
    $mail.Subject = $subject  
    $mail.HTMLBody = $HTMLbody
    $mail.save()
    }

Answer 2

Say your csv has some columns: name, surname, bla-bla, bla-bla, bla-bla.

$data = import-csv C:\filepath\xx.csv | select name, surname
$data | format-table -groupby (key value)