PowerShell to Change CSV row based upon multiple criteria is row

Good Afternoon, Everyone!

I’ve searched through the forums for answer, but everything I’m coming up with is short of what I’m trying to achieve, but great starting spots… I just can’t figure out how to utilize what’s out here to finish it.

Let’s say that I have a CSV file that has 3 columns of data, as show below:

54,R,5,MEETINGS
54,R,25,INDLB
54,R,7,MISCMTA
56,R,9,MAINT
56,R,5,INDLB
56,R,1,MAINT
57,R,1,MAINT
62,R,5,EN
62,R,5,INDLB
62,R,1,MEETINGS

I need the PowerShell script to do the following: If Column1 equals 54,56,62 (and other specified numbers) AND Column4 equals MEETINGS, MISCMTA, MAINT (and other specified codes) THEN change Column4 to UNKNOWN.

What we would end up with is:

54,R,5,UNKNOWN
54,R,25,INDLB
54,R,7,UKNOWN
56,R,9,UNKNOWN
56,R,5,INDLB
56,R,1,UNKOWN
57,R,1,MAINT
62,R,5,EN
62,R,5,INDLB
62,R,1,UNKNOWN

All help is GREATLY appreciated!

Are there column headings in this CSV?

$csv = Import-CSV -Path ("{0}\test.csv" -f [environment]::GetFolderPath("Desktop")) -Header Column1, Column2, Column3, Column4

$numbers =  54,56,62
$codes = "MEETINGS", "MISCMTA", "MAINT"
foreach ($row in $csv) {
    
    if (($numbers -contains $row.Column1) -and ($codes -contains $row.Column4)) {
        "Updating column4 to UNKNOWN"
        $row.Column4 = "UNKNOWN"
    }
}

$csv

Output:

Column1 Column2 Column3 Column4
------- ------- ------- -------
54      R       5       UNKNOWN
54      R       25      INDLB  
54      R       7       UNKNOWN
56      R       9       UNKNOWN
56      R       5       INDLB  
56      R       1       UNKNOWN
57      R       1       MAINT  
62      R       5       EN     
62      R       5       INDLB  
62      R       1       UNKNOWN

Rob,

Thank you!! For some reason though, it’s not writing the changes back to the CSV file. I’ve tried to Set-Conent and Export-CSV, but neither work, which is surely due to my ignorance. What can I add to the end of the script to make it save the changes to the original CSV file?

Can’t tell you how much I appreciate your help!

Sorry, Rob, I’ve moved past the issue that I responded with, but, is there a way to keep the headers (Column1, Column2, Column3, Column4) out of the export?

You could use the following pipe instead of Export-Csv. “Select-Object -Skip 1” will remove the column header for you.

$csv | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip  | Set-Content -Path .\output.csv -Encoding utf8

The solution could vary depending on the version of Powershell you are using. I couldn’t find the a version requirement for ConvertTo-CSV, but it was version 3 or 4 that it became available. Assuming you have those versions, you could do something like this:

$newCsv = $csv | ConvertTo-CSV -NoTypeInformation | Select -Skip 1
$newCSV | Set-Content -Path ("{0}\test_new.csv" -f [environment]::GetFolderPath("Desktop"))

If ConvertTo-CSV is unavailable, you may need to Export-CSV and then use Get-Content on the file:

$exportPath = ("{0}\test_new_2.csv" -f [environment]::GetFolderPath("Desktop"))
$csv | Export-CSV -Path $exportPath -NoTypeInformation
$content = Get-Content -Path $exportPath
$content | Select -Skip 1 | Set-Content -Path $exportPath