PowerShell to Change CSV row based upon multiple criteria is row

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 2 months, 2 weeks ago.

  • Author
    Posts
  • #70137
    Profile photo of Scott Shelton
    Scott Shelton
    Participant

    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!

  • #70143
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Are there column headings in this CSV?

  • #70146
    Profile photo of Rob Simmers
    Rob Simmers
    Participant
    $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
    
    • #70149
      Profile photo of Scott Shelton
      Scott Shelton
      Participant

      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!

  • #70156
    Profile photo of Scott Shelton
    Scott Shelton
    Participant

    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?

    • #70159
      Profile photo of Daniel Krebs
      Daniel Krebs
      Moderator

      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
      
  • #70167
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    

You must be logged in to reply to this topic.