Replace values <0 and >50 in a column of a csv file using powershell

Question

Extreme newbie here.

I have a csv file that shows stock availability in a column called QOH. I'm trying to write a powershell script to change all negative values in the QOH column to 0, and also change any value of 50 or greater to =50. eg: if QOH = -3 , replace with 0. If QOH = 39, then do nothing, and if QOH = 78 replace with 50.

Any code help would be great

For this example, we'll say the csv file is located C:\TEMP Column headers are : SKU,QOH,AVAIL,RRP,BC_id,Group,UPC

This is the attempt to get the <0 to = 0. (i haven't got anything for the >50 yet)

This doesnt work as everything in the QOH column now comes out at 0.

$Path = "C\TEMP\test.csv"
(Import-CSV -Path $Path) | select-object SKU,QOH,AVAIL,RRP,BC_id,Group,UPC | 
ForEach-Object {
    if ([float]$_.QOH -le 0) {
        $_.QOH = '0'
    } Else {
    $_.QOH = $_.QOH
    }
    $_
} | Export-CSV -Path $Path -NoTypeInformation

Answer 1

Not sure if you've found an answer yet, so I'm throwing this out there anyways.

One thing I would point out is you don't need to set 0 to 0 or set $.QOH to $.QOH. Basically, only those less than 0 or greater than 50 get changed - the rest is untouched.

$text = @'
QOH,prop1,prop2
-3,data1,data2
-180,data3,data4
0,data5,data6
28,data7,data8
50,data9,data10
79,data11,data12
'@

$Text | convertfrom-csv | foreach {
    if([float]$_.QOH -lt 0){$_.QOH = 0}
    elseif([float]$_.QOH -gt 50){$_.QOH = 50}
    $_
}

I just put some sample data to make it clear. Here is the output

QOH prop1  prop2 
--- -----  ----- 
  0 data1  data2 
  0 data3  data4 
  0 data5  data6 
 28 data7  data8 
 50 data9  data10
 50 data11 data12