Trying to loop and replace fields in a CSV file in PowerShell. Problems with matching syntax

Solution 1:

In PowerShell, all comparison operators have the form -<operatorShortName>. The operator described by =, on the other hand, is only for assignment of values.

To test for equality between two values, you'd use the -eq operator:

if($userobjects[$counter].'Consumption Period' -eq 'FY22: BLAH') { <# ... #> }

Beware that string comparisons default to case-insensitivity, so "A" -eq "a" is considered $true - to perform explicitly case-sensitive comparisons, use the -ceq ("case-sensitive eq"):

if($userobjects[$counter].'Consumption Period' -ceq 'FY22: BLAH') { <# ... #> }

How do I then take that variable and replace its value based upon the value it contains? (what command would I use after properly matching)?

Now, this is where = is actually the appropriate operator to use!

if($userobjects[$counter].'Consumption Period' -ceq 'FY22: BLAH') {
    # data is corrupt, better replace it with a warning label
    $userobjects[$counter].'Consumption Period' = "<unkown or invalid period specified>"
}

If your goal is to detect/correct any row where the given field does not comply with the expected format, flip the script around and start by describing what the correct value would look like:

if($userobjects[$counter].'Consumption Period' -match '^FY[0-9]{2}: M(0[1-9]|1[012])$'){
    # all is good!
}
else {
    # invalid format encountered - correct it here
}

Here, I'm using the -match regular expression comparison operator to test whether the value found in each CSV record matches the pattern: ^FY[0-9]{2}: M(0[1-9]|1[012])$, which in turn describes:

^             # Start of string
 FY           # the literal string "FY"
   [0-9]{2}:  # two decimal digits, followed by a literal colon
 M            # the literal string "M"
  (?:         # start non-capturing group construct
     0[1-9]   # match a 0 followed by one of digits 1 through 9
    |         # OR
     1[012]   # match a 1 followed by one of digits 0, 1, or 2
  )           # close non-capturing group construct
$             # end of string

To get the current Year/Month in the given format, use Get-Date -Format:

$newLabelCurrentMonth = Get-Date -Format '\F\Yyy: \MMM'

And then assign to the appropriate property on the matching records:

if($userobjects[$counter].'Consumption Period' -match '^FY[0-9]{2}: M(0[1-9]|1[012])$'){
    # all is good!
}
else {
    # invalid format encountered - correct it here
    $userobjects[$counter].'Consumption Period' = $newLabelCurrentMonth
}

Finally, since we've been modifying the objects in the original collection directly, simply export it to CSV again and the changes should be evident:

$userobjects |Export-Csv .\path\to\output_file.csv -NoTypeInformation

For more information about native operators in PowerShell, see the about_Operators and about_Comparison_Operators help topics