Tony Antony Tony Antony - 3 months ago 7
PowerShell Question

Getting value from CSV

I'm writing a PowerShell script to read a CSV. I have everything working so far, it's able to find the value from user input (branch number) and finds the values that have "Y" in the available. Please see 2nd picture.

First picture is my CSV file.

This is what I need help with. How would I get the value of the first available CoreID? In this example, FMD354800000. Once I get the first available CoreId, I want to change the Available to N

enter image description here

enter image description here

$Find = $ImportCSV | Select-String -Pattern $GetBranchNum

$Find -match "Y"

New-ItemProperty -path $CoreIP -name "TTable ID" -PropertyType String -Value "Test" -Force

Answer

Use Import-Csv to import the CSV:

$csv = Import-Csv 'C:\path\to\your.csv'

Since the file seems to be fixed width you'll need to Trim() fields before checking their value.

$branchNumber = '8000'

$first = $csv | Where-Object {
    $_.'Branch Number'.Trim() -eq $branchNumber -and
    $_.Available.Trim() -eq 'y'
} | Select-Object -First 1

This filters the CSV for records with the given branch number that have a value Y in the field Available and selects the first matching record.

Then change the value of the Available field of that record:

$first.Available = 'N'

Demonstration:

PS C:\> $csv = Import-Csv 'C:\path\to\sample.csv'
PS C:\> $csv

Branch Number      CoreID            Available
-------------      ------            ---------
8000               FMD354800000      Y
8000               FMD354800001      Y
8000               FMD354800002      N

PS C:\> $first = $csv | Where-Object {
>>   $_.'Branch Number'.Trim() -eq '8000' -and
>>   $_.Available.Trim() -eq 'y'
>> } | Select-Object -First 1
>>
PS C:\> $first

Branch Number      CoreID            Available
-------------      ------            ---------
8000               FMD354800000      Y

PS C:\> $first.Available = 'N'
PS C:\> $first

Branch Number      CoreID            Available
-------------      ------            ---------
8000               FMD354800000      N

PS C:\> $csv

Branch Number      CoreID            Available
-------------      ------            ---------
8000               FMD354800000      N
8000               FMD354800001      Y
8000               FMD354800002      N

Neither Select-String nor the -match operator are particularly useful in this context, so don't use them.

Comments