Search CSV with PowerShell

Question

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
}

Ansgar Wiechers · Accepted Answer · 2017-07-13 13:12:31Z

2

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

edited Jul 13, 2017 at 13:12

answered Jul 13, 2017 at 12:26

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Amrit Sharma Over a year ago

But How Do I update the Status of that LineUri once I use this Line Uri..

Amrit Sharma Over a year ago

Can you please let me know how can i update the status of the returned LineUri

Amrit Sharma Over a year ago

So when I do $csv | Export-Csv -Path $newpath -NoTypeInformation , I still get that LineUri as Spare

Ansgar Wiechers Over a year ago

I seriously doubt that. Please provide evidence. Update your question with your modified code.

Amrit Sharma Over a year ago

Found the problem, in foreach loop, $$firstAvailableSpare should be $firstAvailableSpare.LineUri

|

Collectives™ on Stack Overflow

Search CSV with PowerShell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related