PowerShell to Change CSV row based upon multiple criteria is row

[Scott Shelton Participant Points: 0Rank: Member]

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!

[Don Jones Keymaster Points: 1,811Rank: Community Hero]

Are there column headings in this CSV?

[Rob Simmers Participant Points: 492Rank: Contributor]

$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

[Scott Shelton Participant Points: 0Rank: Member]

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!

[Scott Shelton Participant Points: 0Rank: Member]

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?

[Daniel Krebs (Dan1el42) Moderator Points: 24Rank: Member]

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

[Rob Simmers Participant Points: 492Rank: Contributor]

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

[Author]

Posts