CODEWITHSUNDEEP
powershellcsv
MJB
MJB

Reputation: 41

Powershell: Find any value in a CSV column and replace it with a single value

I have a CSV file where I have to find any non-blank value in 2 specific columns and replace them with 'Yes'

My data looks like this where it can have either both blank, value in either column, or in both.

Letter Grade Numeric Grade
A
10
C 5

I want it to look like this when I'm done

Letter Grade Numeric Grade
Yes
Yes
Yes Yes

I have 2 problems, addressing columns that have a space in the name (tried wrapping with " and ' and {) and regex to match any non-empty value. It works with the code below to simply replace a and if the column is Letter instead of Letter Grade.

I tried .+ to match anything in the cell, but I get no matches.
Thanks in advance!

Import-Csv -Path ".\test.csv"| ForEach-Object {
    if ($_.Letter -eq 'a') {
        $_.Letter = 'Yes'
    }
    $_
} | Export-Csv .\poop2.csv -Encoding UTF8

Upvotes: 2

Views: 486

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 59798

You could handle this programmatically by, first, collecting all property names from the first object (done via accessing of intrinsic member PSObject in this example) and then enumerating each property of each object coming from the pipeline and checking if it matches \S (any non-whitespace character).

Import-Csv path\to\csv.csv | ForEach-Object { $isFirstObject = $true } {
    if($isFirstObject) {
        $properties    = $_.PSObject.Properties.Name
        $isFirstObject = $false
    }
    foreach($property in $properties) {
        if($_.$property -match '\S') {
            $_.$property = 'Yes'
        }
    }
    $_
} | Export-Csv path\to\newcsv.csv -NoTypeInformation

If, instead of programmatically gathering the object's property names, you wanted to use specific / hardcoded properties, the code would be simpler:

$properties = 'Letter Grade', 'Numeric Grade'
Import-Csv path\to\csv.csv | ForEach-Object {
    foreach($property in $properties) {
        if($_.$property -match '\S') {
            $_.$property = 'Yes'
        }
    }
    $_
} | Export-Csv path\to\newcsv.csv -NoTypeInformation

Upvotes: 2

Related Questions