CODEWITHSUNDEEP
powershellcsvscriptingpowershell-2.0export-to-csv
Amrit Sharma
Amrit Sharma

Reputation: 1916

Search CSV with PowerShell

I am trying to do simple task with CSV using the PowerShell but its really not working for me.

I have CSV file 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   Spare          Support      NSW
Test 2  tel:+61396176103;ext=6103   Used           WareHouse    SA
Test 2  tel:+61396176104;ext=6104   Used           Sales        SA
Test 2  tel:+61396176105;ext=6105   Spare          Support      VIC
Test 2  tel:+61396176106;ext=6106   Spare          WareHouse    VIC
Test 2  tel:+61396176107;ext=6107   Spare          Support      VIC

I am trying to search the LineUri whose status is "Spare" based on location.

So criteria will be like this:

Status = "Spare" 
BusinessUnit = "WareHouse"
Location = "VIC"

This should return me "tel:+61396176106;ext=6106" as spare.

Once I have this Spare LineUri, I have another logic that will be executed and then change the status of that particular LineUri to "Used".

I have this code which is giving me the lineUri, but I am not sure how can I take out that return value from ForEach-Object loop and used in another function.

$path     = Split-Path -parent $MyInvocation.MyCommand.Definition
$newpath  = $path + "\PhoneData.csv"
# Array of spare numbers that meet your criteria
$firstAvailableSpare

# criteria
$currentStatus = "Spare"
$userBusinessUnit = "WareHouse"
$userLocation = "VIC"

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

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

    if (($_.Status -eq $currentStatus) -and ($_.BusinessUnit -eq $userBusinessUnit) -and ($_.Location -eq $userLocation)) { 
        $firstAvailableSpare = $_ # Assign the first
        break # break the loop so we don't get additional hits
    }
}

try {
    #use the return value and update the csv so that its status is changed to Used
} catch {
    #do something else if failed
}

Upvotes: 2

Views: 5062

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Use a Where-Object filter to select rows from a CSV based on multiple fields, and use Select-Object to restrict the results.

$firstAvailableSpare = $csv | Where-Object {
    $_.Status -eq $currentStatus -and
    $_.BusinessUnit -eq $userBusinessUnit -and
    $_.Location -eq $userLocation
} | Select-Object -Expand LineUri -First 1

You can modify the row with that LineUri like this:

foreach ($row in $csv) {
    if ($row.LineUri -eq $firstAvailableSpare) {
        $row.Status = 'Used'
    }
}

Upvotes: 2

Related Questions