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

I have a CSV file that I want to loop through each line and change out a value if it matches. However I am not matching properly.

In the 4th column the date is represented as above "FY22: M01" Each month this will change. I believe there is a problem with my syntax on representing the CSV field I am trying to select.

So my questions are as follows:

Can someone please review this and tell me why I am matching on the first "If" statement incorrectly instead of matching on the "elseif" statement? I believe it has to do with the IF and parentheses.
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)?

A sample of the data is below (Header line included)

Invoice Number,Customer   AdSeq,Date-Loaded,Consumption Period,Usage Year,Usage Quarter,Usage Month, Service, Units, Job Number,Description,Environment,Exception Tag (Binary yes/no),Attribute 1,Attribute 2,Attribute 3,Attribute 4,Attribute 5,Attribute 6,Attribute 7,Attribute 8,Attribute 9,Attribute 10,Attribute 11,Attribute 12,Attribute 13,Approved Rate Amount, Charge Amount (Units x Approved Rate)

22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup 
22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup 
22DS00009,ABC01-001,,FY22: M01,,,,Backup
22DS00009,ABC01-001,,FY22: M01,,,,Backup 
22DS00009,ABC01-001,,FY22: M01,,,,Backup

The field I am trying to match on in "Consumption Period"

$userobjects = Import-Csv C:\FileLocation

$counter=0
foreach ($i in $userobjects)
{

echo $userobjects[$counter].'Comsumption Period'
    If ($userobjects[$counter].'Consumption Period' = 'FY22: BLAH')
        #{}
        #{echo "If I see this its not working"}
        {echo $userobjects[$counter].'Consumption Period'}
    
    elseif ($userobjects[$counter].'Consumption Period' = 'FY22: M01')
        #{}
        #{echo "working"}
        {echo $userobjects[$counter].'Consumption Period'}

    $counter=$counter 1
    #echo  $counter

CodePudding user response：

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