none
Comparing rows in csv with matching columns names RRS feed

  • Question

  • Hello, I am stuck on a particular issue. I am not trying to compare multiple csv files, I just have one. I have a csv file that has multiple entries for each "provided_name" and "IP_Address" like so:

    provided_name,IP_Address

    server1,192.168.1.1

    server2,192.168.1.2

    server3,192.168.1.3

    server1,192.168.1.5

    server3,192.168.1.3

    etc..

    All I would like to do is have the script show me using provided_name as a key where fields like IP_Address are different from each other, and log those in a separate csv. I am getting confused by how I am supposed to run this. I figured something like

    Import-Csv -Path "C:\Users\me\Desktop\Hostnames.csv" |
         Group-Object provided_name |

    and at that point it creates a hash table with a name and group, which is more complex then I think I should need to go. I have also tried doing a

    foreach ($item in $csv) | where-object {$.provided_name -match $csv.provided_name} | Compare-Object

    and try dumping that to a csv without much luck. Maybe this is stupidly obvious to everyone but me, but some help getting over this rut would be greatly appreciated!


    • Edited by JEngstrom Thursday, September 29, 2016 5:12 PM Edited for clairity
    Thursday, September 29, 2016 4:11 PM

Answers

  • Oh, you want to skip if the row is repeated with the same IP address. How about this?


    $hash = @{}
    $rows = Import-Csv "MyData.csv"
    foreach ( $row in $rows ) {
      if ( -not $hash.Contains($row.IP_Address) ) {
        $hash.Add($row.IP_Address, $row.provided_name)
      }
      else {
        if ( $row.IP_Address -ne $hash.IP_Address ) {
          "Duplicate IP_Address for {0}" -f $row.provided_name
        }
      }
    }


    -- Bill Stewart [Bill_Stewart]


    Thursday, September 29, 2016 6:49 PM
    Moderator

All replies

  • Import-Csv file | Group provided_name |where {$_.Count -gt 1}


    \_(ツ)_/

    Thursday, September 29, 2016 4:32 PM
  • I was looking for IP_Address that were different from where provided name was the same. I edited my csv example above to reflect that; I should have been more clear. Not all duplicates are incorrect, so a count will not help me in this scenario. I added two entries of server3 to reflect that.
    Thursday, September 29, 2016 5:15 PM
  • The also enumerate the groups and filter out all where the IPs are all the same.


    \_(ツ)_/

    Thursday, September 29, 2016 5:19 PM
  • Are you talking about this?


    $hash = @{}
    $rows = Import-Csv "MyData.csv"
    foreach ( $row in $rows ) {
      if ( -not $hash.Contains($row.IP_Address) ) {
        $hash.Add($row.IP_Address, $row.provided_name)
      }
      else {
        "Duplicate IP_Address for {0}" -f $row.provided_name
      }
    }
    


    -- Bill Stewart [Bill_Stewart]

    Thursday, September 29, 2016 5:21 PM
    Moderator
  • This also works

    Import-Csv file | select * -unique  Group provided_name |where {$_.Count -gt 1}

    Now only groups that have different values are a result.

    You can also do this:

    $results = Import-Csv file | select * -unique  Group provided_name |where {$_.Count -gt 1}

    Import-Csv file | ?{$_.provided_name in $results }


    \_(ツ)_/


    • Edited by jrv Thursday, September 29, 2016 5:25 PM
    Thursday, September 29, 2016 5:22 PM
  • The idea I am looking at above is that this script would run against this csv, and it would report back that server1 (because it is listed twice with different IP Address value) may have issues and would record this information in a separate csv.
    Thursday, September 29, 2016 6:06 PM
  • This is interesting, but the result I got back was everything as I did not see anything to compare the IP addresses to determine if they were different or not.
    Thursday, September 29, 2016 6:07 PM
  • Import-Csv file | select * -unique  Group provided_name |where {$_.Count -gt 1} | select name

    You need to take part in this and try to learn what is happening in PowerShell.

     


    \_(ツ)_/

    Thursday, September 29, 2016 6:10 PM
  • I got some more interesting results toying with your suggestion, but not sure what to do at this point:

    $results = Import-Csv -Path "file.csv" |
         Select-Object * -Unique | Group-Object provided_name | Where-Object {$_.Count -gt 1 }

         Import-Csv "file.csv" | Where-Object {$_.ip_address -notmatch $results.Group.ip_address }

    I had something like this as well, but didn't make it past here. I keep running into the same wall, not sure how to group by provided_name, and show me all with the same provided name that have different IP_Address.

    Or more simply put, for every line that has the same provided_name show me any line that have IP_Address that is different from each other. Make more sense?

    Thursday, September 29, 2016 6:18 PM
  • Import-Csv file | Select-Object * -unique | Group-object provided_name | Where-Object {$_.Count -gt 1} | Select-Object name

    I am assuming this is what you meant? This just dumps out the list of provided_name which has more than 1 line. It does not do any checking of consistent values in any of the other fields in each entry.

    I have been trying to figure this out, for a few days now. What I don't see is any logic that is comparing multiple lines of identical provided_name entries, and comparing the IP_Address of each one to make sure they are identical. I am not looking for anyone to hand me an answer, I am looking for help with this portion of the logic. I have not been able to do it so far, despite my many attempts.

    Thursday, September 29, 2016 6:33 PM
  • Oh, you want to skip if the row is repeated with the same IP address. How about this?


    $hash = @{}
    $rows = Import-Csv "MyData.csv"
    foreach ( $row in $rows ) {
      if ( -not $hash.Contains($row.IP_Address) ) {
        $hash.Add($row.IP_Address, $row.provided_name)
      }
      else {
        if ( $row.IP_Address -ne $hash.IP_Address ) {
          "Duplicate IP_Address for {0}" -f $row.provided_name
        }
      }
    }


    -- Bill Stewart [Bill_Stewart]


    Thursday, September 29, 2016 6:49 PM
    Moderator
  • Import-Csv file | 
    	Select-Object * -unique | 
    	Group-object provided_name | 
    	Where-Object {$_.Count -gt 1} | 
    	Select-Object name, @{n='Addresses';e={$_.Group -join '|'}
    
    
    
    

    You need to learn PowerShell.  The answer has always been here but you keep changing the rules.


    \_(ツ)_/


    • Edited by jrv Thursday, September 29, 2016 6:51 PM
    Thursday, September 29, 2016 6:50 PM