[FIXED] How to check empty row from csv column using PowerShell

Issue

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

    }

}

Solution

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
    }
  }

Answered By - mklement0

Answer Checked By - Willingham (PHPFixing Volunteer)