powershellcsvpowershell-4.0

How to check empty row from csv column using PowerShell


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:


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