I've mixed data(some row are empty) like this in my csv column called "Exchange Mailboxes".
Exchange Mailboxes
Include:[john.doe@outlook.com]
Include:[david.smith@outlook.com]
Include:[kevin.love@outlook.com]
I'm try to check if the row is empty first. If it's empty then just assigned empty string for those row. If a particular csv row is not empty then get their Name and Id using EXOMailbox.
Another word will be, how do I skip an empty rows?
I tried it like this but some reason it's not working.
$importFile = Import-Csv -Path "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv"
foreach ($csvFile in $importFile){
if([string]::IsNullOrEmpty($csvFile.'Exchange Mailboxes')){
$userName = ""
$userId = ""
}else{
$exoMailbox = $csvFile.'Exchange Mailboxes'.Split([char[]]@('[', ']'))[1]
$exoUser = Get-EXOMailbox -Filter "PrimarySmtpAddress -eq '$exoMailbox'"
$userName = $exoUser.DisplayName
$userId = $exoUser.Identity
}
}
If you want to exclude rows that have an empty Exchange Mailboxes
column value:
$importFile =
Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
Where-Object 'Exchange Mailboxes' -ne ''
If you want to exclude rows where all columns are empty (which would also work with your (atypical) single-column CSV):
$importFile =
Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
Where-Object { -join $_.psobject.Properties.Value }
Note:
Import-Csv
parses a CSV file's rows into [pscustomobject]
instances whose properties reflect the CSV's column values invariably as strings.
$_.psobject.Properties.Value
uses the intrinsic .psobject
property to reflect on all properties of an object, and .Properties.Value
returns all property values.
The unary form of the -join
operator directly concatenates all - by definition string - values and returns the result as a single string.
Thus, if all column values are empty, the result is the empty string (''
); otherwise, the string is non-empty.
Where-Object
, when given a script block ({ ... }
), implicitly coerces its output to [bool]
($true
or $false
); in PowerShell, converting any non-empty string to [bool]
yields $true
, whereas the empty string yields $false
.
Applying the above in combination with extracting just the email addresses from the Exchange Mailboxes
column values:
$emailAddresses =
Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
Where-Object { -join $_.psobject.Properties.Value } |
ForEach-Object { ($_.'Exchange Mailboxes' -split '[][]')[1] }
With your sample input, outputting $emailAddresses
then yields:
john.doe@outlook.com
david.smith@outlook.com
kevin.love@outlook.com
Applied to what I presume was the intent of your original code:
$customObjects =
Import-Csv "C:\AuditLogSearch\Dis\Modified-Audit-Log-Records.csv" |
Where-Object { -join $_.psobject.Properties.Value } |
ForEach-Object {
$exoMailbox = ($_.'Exchange Mailboxes' -split '[][]')[1]
$exoUser = Get-EXOMailbox -Filter "PrimarySmtpAddress -eq '$exoMailbox'"
# Construct and output a custom object with the properties of interest.
[pscustomobject] @{
UserName = $exoUser.DisplayName
UserId = $exoUser.Identity
}
}