Powershell - Compare CSV 1 to CSV 2 and then update CSV1-CodePudding

I am not looking for a writing service but please can someone point me in the right direction as I am completely at a lost as to how to proceed.

Overview I have a CSV which contains a lot of data, some of which comes from a script and some in manually imputed. I can run the script and get new data which is good. What I would like to do is find a way to compare the orginal CSV 1 to the new CSV 2 and update CSV 1.

Code I currently have 

$Vips_to_check = @{}
                 Import-Csv 'C:\Users\user\Documents\20221201\Netscaler VIPs per Cluster_edited - Raw Data.csv' |
                 Where-Object {$_.PRD -match "No PRD code from VIP IP and VIP has no backend IPs" -or
                               $_.PRD -match "No PRD code found from VIP or backend IPs" -or
                               $_.PRD -match "No PRD code found from backend IPs" -and
                               $_.ipv46 -notcontains "0.0.0.0"}                                                 |
                                        
$Results_from_PIM = Import-Csv 'C:\Users\user\Documents\20221201\VIP-Owners_edited.csv'

Both of the CSV's have the same headers and layout which is good. I assume!

CSV 1

Name IPV46 Port Curstate Suggested PRD Display Name tech Owner Slack Channel Support Email

name 1 1.2.3.4 8080 Down No No No No No No No

CSV 2

Name IPV46 Port Curstate Suggested PRD Display Name tech Owner Slack Channel Support Email

name 1 1.2.3.4 8080 Down No PRD123 TMOL Gary TMOL Support [email protected] nsr.sys

I would guess at creating a hashtable but I just can't seem to get my head around the format of them. I tried

$ht = $Results_from_pim @{}
      $_.Name = (cant figure out how to reference the cell)
      $_.PRD = 
    $_.("Display Name") 
    $_.("Tech Owner")

Once I have the data in the hash table how do I overwrite the CSV 1 data?

Any points or guides would be great. I have tried reading up on https://learn.microsoft.com/en-gb/powershell/scripting/learn/deep-dives/everything-about-hashtable?view=powershell-7.3 and https://learn.microsoft.com/en-us/powershell/scripting/learn/deep-dives/everything-about-pscustomobject?view=powershell-7.3

But that left me even more confused.

At the moment the difference is only 4 or 5 entries and it would of been quicker for me to manually edit in excel but as this script gets larger I can see it being more time consuming to do manually.

As always thank you.

UPDATE

$ht = @{}
foreach ($item in $Results_from_PIM) {
          "name = $($item.name)" 
           "prd = $($item.PRD)"
           "Display Name = $($item.'Display Name')"
           "Tech Owner = $($item.'Tech Owner')"
           "Slack Channel = $($item.'Slack Channel')"
           "Support Email = $($Item.'Support Email')"
           }

I have created the hash table that I wanted from the CSV 2. Just got to get it to compare to CSV 1.

Update 2

Further to @theo request I have adjusted the question. Also to clarify When I want to merge the CSV it is based on matching the Name, IPV46 and Port on both CSV and then moving the updated data from CSV2 into CSV1.

CodePudding user response：

You can do that with the code below (no extra module needed):

$csv1 = 'C:\Users\user\Documents\20221201\Netscaler VIPs per Cluster_edited - Raw Data.csv'
$csv2 = 'C:\Users\user\Documents\20221201\VIP-Owners_edited.csv'

$Results_from_PIM = Import-Csv -Path $csv2
$newData = Import-Csv -Path $csv1 | ForEach-Object {
    $search  = $_.Name   $_.IPV46   $_.Port   # combine these fields into a single string
    $compare = $Results_from_PIM | Where-Object { ($_.Name   $_.IPV46   $_.Port) -eq $search }
    if ($compare) {
        # output the result from csv2
        $compare
    }
    else {
        # output the original row from csv1
        $_
    }
}

# now you can save the updated data to a new file or overwrite csv1 if you like
$csv3 = 'C:\Users\user\Documents\20221201\VIP-Owners_Updated.csv'
$newData | Export-Csv -Path $csv3 -NoTypeInformation

P.S. Please read about Formatting

CodePudding user response：

After being direct to In PowerShell, what's the best way to join two tables into one? by @jdweng. I performed the following which seems to have meet my requirements

Install-Module -Name JoinModule -Scope CurrentUser

$Vips_to_check = Import-Csv 'C:\Users\user\Documents\20221201\Netscaler VIPs per Cluster - Raw Data.csv'
                                                               
$Results_from_PIM = Import-Csv 'C:\Users\user\Documents\20221201\VIP-Owners.csv'

$Vips_to_check | Update-Object $Results_from_PIM -On name, Ipv46, port | Export-Csv 'C:\Users\user\Documents\20221201\Final_data1.csv'

Going to do further testing with larger data sets but appears to work as required.