CODEWITHSUNDEEP
powershell
asthmatic_weasel
asthmatic_weasel

Reputation: 31

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

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.

Upvotes: 0

Views: 138

Answers (2)

asthmatic_weasel
asthmatic_weasel

Reputation: 31

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.

Upvotes: 0

Theo
Theo

Reputation: 61093

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

Upvotes: 1

Related Questions