How to update specific rows in a CSV file

Welcome Forums General PowerShell Q&A How to update specific rows in a CSV file

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

 
Participant
1 year, 8 months ago.

  • Author
    Posts
  • #67291

    Participant
    Points: 0
    Rank: Member

    Hello,

    I have 2 CSV files, like the below:
    CSV1:
    GUID,user,device,date,status
    xyz1,testuser1,testdevice1,yesterday,allowed
    xyz2,testuser2,testdevice2,yesterday,denied

    CSV2:
    GUID,user,device,date,status
    xyz1,testuser1,testdevice1,today,allowed
    xyz3,testuser3,testdevice3,today,allowed

    I need a script to be run on CSV2, and check for each line on CSV2 if the GUID exist on some line on CSV1, if the exact same GUID exist, the entire line on CSV2 should be copied and overwrite the exact line with the same GUID on CSV1,
    If CSV2 contain a row with a GUID which doesn't exist on CSV1, the entire row should be appended to CSV1,

    Could anyone please provide me some help, how the powershell can copy and overwrite to a specific line in CSV file without appending?

    Thanks!
    Amichai

  • #67294

    Participant
    Points: 269
    Helping Hand
    Rank: Contributor

    What did you try so far? Did you search for what you need? I'm almost sure even here in the forum we had a lot of at least similar requests you could adapt to your needs. Search for "combine csv" ... 😉

  • #67300

    Participant
    Points: 91
    Rank: Member
    #Input
    $File1Lines = Import-Csv .\csv1.csv
    $File2Lines = Import-Csv .\csv2.csv
    
    # check for each line on CSV2 if the GUID exist on some line on CSV1, 
    # the entire line on CSV2 should be copied and overwrite the exact line with the same GUID on CSV1,
    $NewCSV = foreach ($Line in $File1Lines) {
        $MatchFound = $false
        $File2Lines | % {
            if ($Line.GUID -match $_.GUID) {
                $_    # Get the entire line from CSV2
                $MatchFound = $true
            } 
        }
        if (! $MatchFound) { $Line } # Get the entire line from CSV1    
    }
    
    # If CSV2 contain a row with a GUID which doesn't exist on CSV1, the entire row should be appended to CSV1,
    $NewCSV += foreach ($Line in $File2Lines) {
        $MatchFound = $false
        $File1Lines | % {
            if ($Line.GUID -match $_.GUID) { $MatchFound = $true } 
        }   
        if (! $MatchFound) { $Line } # Append line from CSV2    
    }
    
    # Write $NewCSV back to CSV1
    $NewCSV | Export-Csv .\csv1.csv -Force -NoType 
    
  • #67306

    Participant
    Points: 269
    Helping Hand
    Rank: Contributor

    @Sam – don't you feel like you did someone's homework now? You're just too nice. 😉

    • #67339

      Participant
      Points: 91
      Rank: Member

      @ Olaf, agreed 🙂 beets playing solitaire.. lol

The topic ‘How to update specific rows in a CSV file’ is closed to new replies.