How to update specific rows in a CSV file

This topic contains 4 replies, has 3 voices, and was last updated by  Sam Boutros 8 months, 3 weeks ago.

  • Author
    Posts
  • #67291

    AmichaiPinto
    Participant

    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

    Olaf Soyk
    Participant

    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

    Sam Boutros
    Participant
    #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

    Olaf Soyk
    Participant

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

    • #67339

      Sam Boutros
      Participant

      @ Olaf, agreed 🙂 beets playing solitaire.. lol

You must be logged in to reply to this topic.