Author Posts

March 26, 2017 at 3:27 pm

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

March 26, 2017 at 6:05 pm

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" ... 😉

March 26, 2017 at 7:09 pm

#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 

March 26, 2017 at 9:59 pm

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

March 27, 2017 at 10:39 am

@ Olaf, agreed 🙂 beets playing solitaire.. lol