Search and find data from CSV based on Criteria

Question

I am creating powershell script to create skype for business user account. I am trying to find the avaialble number from CSV file which looks for Status, Business Unit and location to find correct LineUri to be used.

I am using following code which always use the first SPARE number and doesn't validate the location and business unit to find the line uri.

$path     = Split-Path -parent $MyInvocation.MyCommand.Definition
$newpath  = $path + "\PhoneData.csv"
$LineUri = @()
$Status = @()
$BusinessUnit = @()
$Location = @()

$csv = Import-Csv $newpath -Delimiter ","

$csv |`
    ForEach-Object {
        $LineUri += $_.LineUri
        $Status += $_.Status
        $BusinessUnit +=$_.BusinessUnit
        $Location +=$_.Location
    }

$currentStatus = "Spare"
$userBusinessUnit = "Support"
$userLocation = "WA"


if ($Status -eq $currentStatus -And $BusinessUnit -eq $userBusinessUnit -And $Location -eq $userLocation )
    {    
    $Where = [array]::IndexOf($Status, $currentStatus)
    $AvailableURI = $LineUri[$Where]    
    #Write-Host "Next Uri Available: " $LineUri[$Where]

    $changeWhere = [array]::IndexOf($LineUri, $AvailableURI)
    #Write-Host "Next Uri Available: " $Status[$changeWhere]


    Try
    {
        Enable-CsUser -identity sudip.sapkota -RegistrarPool "s4b-fe01.tapes.com" -SipAddressType SamAccountName -sipdomain "tapes.com"
        Set-CsUser -Identity sudip.sapkota -EnterpriseVoiceEnabled $true -LineURI $AvailableURI
        Grant-CsDialPlan -Identity sudip.sapkota -PolicyName 'DialPlan'
        Grant-CsVoicePolicy -Identity sudip.sapkota -PolicyName 'My VoicePolicy'

        Write-Host "[INFO]`t Lync Enterprise account for user sudip.sapkota has been created with sip : $AvailableURI" -ForegroundColor "Green"
              "[INFO]`t Lync Enterprise account for user sudip.sapkota has been created with sip : $AvailableURI" | Out-File $log -append

        $i = $changeWhere    
        $csv[$i].Status = 'Used'

        $csv | Export-Csv -Path $newpath -NoTypeInformation

        Write-Host "[INFO]`t PhoneData CSV has been updated" -ForegroundColor "Green"
              "[INFO]`t PhoneData CSV has been updated" | Out-File $log -append
    }

    catch
    {
        Write-Host "[ERROR]`t Oops, something went wrong: $($_.Exception.Message)`r`n" -ForegroundColor "Red" 
                   "[WARNING]`t Oops, something went wrong: $($_.Exception.Message)" | Out-File $log -append
    }

    }

else
{

  Enable-CsUser -identity sudip.sapkota -RegistrarPool "s4b-fe01.tapes.net" -SipAddressType SamAccountName -sipdomain "tapes.net"
  Write-Host "[INFO]`t No Spare LineURI, user has been created as PC-to-PC only" -ForegroundColor "Yellow"
          "[INFO]`t No Spare LineURI, user has been created as PC-to-PC only" | Out-File $log -append
}

My CSV looks like this.

Name        LineUri                     Status  BusinessUnit    Location
Test 1      tel:+61396176100;ext=6100   Spare   Sales           VIC
Test 2      tel:+61396176101;ext=6101   Spare   Sales           VIC
Test 2      tel:+61396176102;ext=6102   Used    Sales           NSW
Test 2      tel:+61396176103;ext=6103   Spare   Support         WA
Test 2      tel:+61396176104;ext=6104   Spare   Support         WA
Test 2      tel:+61396176105;ext=6105   Used    Action          VIC
Test 2      tel:+61396176106;ext=6106   Spare   Suppot          VIC
Test 2      tel:+61396176107;ext=6107   Spare   Action          VIC

Can someone help to find my mistake?

As I am manually feeding the input, the test input are

$currentStatus = "Spare"
$userBusinessUnit = "Support"
$userLocation = "WA"

So I need to find the LineURI which is SPARE, whose location is WA and whose BusinessUnit is Support.

I should get tel:+61396176103;ext=6103 as LineURI

@gms0ulman I have updated my output in the question itself – Amrit Sharma Jul 12 '17 at 13:09 — Amrit Sharma, Jul 12 '17 at 13:09

Ty Savercool · Answer 1 · 2017-07-13T15:59:38.363

I reworked your logic a bit. I felt that you could do all of this inside the foreach loop. For clarity, this does gather the data in the loop and then leaves you with an array to work with. You could just nest your actions inside the loop directly and break out or you could keep it like this and just execute your work against the array item after.

# Array of spare numbers that meet your criteria
$firstAvailableSpare

# criteria
$currentStatus = "Spare"
$userBusinessUnit = "Support"
$userLocation = "WA"


$csv |`
    ForEach-Object {
        $LineUri += $_.LineUri
        $Status += $_.Status
        $BusinessUnit +=$_.BusinessUnit
        $Location +=$_.Location
    $currentStatus = "Spare"
    $userBusinessUnit = "Support"
    $userLocation = "WA"


    if ( ($_.Status -eq $currentStatus) -And ($_.BusinessUnit -eq $userBusinessUnit) -And ($_.Location -eq $userLocation) ) { 
        $firstAvailableSpare = $_ # Assign the first
        $_.Status = "Used"

        continue # break the loop so we don't get additional hits

    }
}

$csv | Export-Csv -Path $newpath -NoTypeInformation
$firstAvailableSpare # <-- your first available spare
$firstAvailableSpare.LineUri # <-- the URI you want

edit 1: Updated for CSV export requirement

edit 2: Changed break to continue. WARNING: For some reason this breaks the ISE's debugging functionality. Stepping will cease and future break points will be ignored. I don't know why this is but it is out of scope for your goal.

This is perfect , but how do I update the CSV file back so that the Status can be changed from SPARE to Used for that particular LineUri.. — Amrit Sharma, Jul 12 '17 at 13:53
I had not come across this interesting behavior with break vs continue in foreach-object. I have updated the script with continue to resolve this issue. Details here if you are curious: https://stackoverflow.com/questions/7760013/why-does-continue-behave-like-break-in-a-foreach-object — Ty Savercool, Jul 13 '17 at 16:00

Search and find data from CSV based on Criteria

1 Answers1