How to update specific rows in a CSV file

Tagged: 

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Sam Boutros Sam Boutros 1 month ago.

  • Author
    Posts
  • #67291
    Profile photo of AmichaiPinto
    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
    Profile photo of Olaf Soyk
    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
    Profile photo of Sam Boutros
    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
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

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

    • #67339
      Profile photo of Sam Boutros
      Sam Boutros
      Participant

      @ Olaf, agreed 🙂 beets playing solitaire.. lol

You must be logged in to reply to this topic.