PowerShell to Change CSV row based upon multiple criteria is row

Welcome Forums General PowerShell Q&A PowerShell to Change CSV row based upon multiple criteria is row

This topic contains 6 replies, has 4 voices, and was last updated by

 
Participant
1 year, 6 months ago.

  • Author
    Posts
  • #70137

    Participant
    Points: 0
    Rank: 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!

  • #70143

    Keymaster
    Points: 1,638
    Helping HandTeam Member
    Rank: Community Hero

    Are there column headings in this CSV?

  • #70146

    Participant
    Points: 160
    Helping Hand
    Rank: 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

      Participant
      Points: 0
      Rank: 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!

  • #70156

    Participant
    Points: 0
    Rank: 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?

    • #70159

      Moderator
      Points: 24
      Team Member
      Rank: 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
      
  • #70167

    Participant
    Points: 160
    Helping Hand
    Rank: 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
    

The topic ‘PowerShell to Change CSV row based upon multiple criteria is row’ is closed to new replies.