Advanced comparaison for 2 csv

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #5128

    by spama at 2012-09-20 07:27:30

    Hello,

    I read a lot of topics of this forum but i don't find my wanted information.
    I am looking for the PS code which will permit me to do the following thing. But before, i just briefly sum up my needed.
    I have 2 excels files, which have the same structure (Colum1 – Server(varchar) ; Status (Int) ; Warrantly (varchar) ; Column D to column J (Bool : TRUE/false)
    Column D to J show the presence of a component (print-service by example ) or not.

    As i am bad in VB script i think to export it in .CSV and compare the 2 files in PS with the cmdlet Compare-object.
    But, but, but...

    If my files have the same structure, they don't have the same content.
    Files A and B are sort A to Z.
    And Files A doen't have some servers contained in file B and File B doen't have some servers contained in file A.

    So if i compare my 2 CSV object only for Column D to J ( True in A and TRUE in B ? or not.. )
    PowerShell will show me the differences between A and B for these column but the results includes the servers which are not present in each file.

    So i would like to run a script which will read the File A and B line per line,
    check if the Server name is the same for the 2 files and then check if the Column D to J are the same.

    At least, the result will be export in a new CSV with the adequat structure. This last point is not a problem but I aml stopped on the double condition : Check the name server and THEN check what i want.

    If you have some idea, ....

    Thank you in advance for your reading, please excuse my english if it has some faults ...

    Regards

    by poshoholic at 2012-09-21 08:33:13

    I think you'll want a hashtable for lookup of the servers by name. Something like this:
    $serversA = @{}
    $serversB = @{}
    Import-Csv C:\fileA.csv | ForEach-Object {$serversA[$_.Server] = $_} # Assumes the server names are in a "Server" column
    Import-Csv C:\fileB.csv | ForEach-Object {$serversB[$_.Server] = $_} # Assumes the server names are in a "Server" column
    foreach ($server in $serversA.Keys | Sort-Object) {
    if (($serversB.ContainsKey($server)) -and (Compare-Object $serversA[$server] $serversB[$server])) {
    # Do the individual property comparison between $serversA[$server] and $serversB[$server] here since you know that they in both files but with different properties at this point
    }
    }

    by spama at 2012-09-24 02:28:16

    Thank You Poshoholic !
    I never thought about that!

    My comparaison is OK now, it works !
    Just an other thing, the result is a screen with only TRUE or FALSE. So y tried to display the name of the server concerned by the compare-object.
    The only way to display name server as i want is the following , but all servers are displayed and I don't find How to display Only the concerned server

    foreach ($server in $serversA.Keys | Sort-Object) {
    Write-Host $server #Write the server name
    if (($serversB.ContainsKey($server)) -and (Compare-Object $serversA.keys $serversB.keys)) {

    compare-object -ref $serversA[$server].RIS -diff $serversB[$server].RIS -PassThru | Format-List

    }
    }

    I tried severals ways but without success.
    In advance thank you

    by spama at 2012-09-24 02:42:19

    I try it too :

    compare-object -ref $serversA[$server].RIS -diff $serversB[$server].RIS -PassThru | where { $serversA[$server].RIS -ne $serversB[$server].RIS } | Format-Custom -Property "Name"
    Format-Custom -Property $servers
    Format-Custom $servers

    If you have any ideas ...

    by poshoholic at 2012-09-24 06:52:10

    Move your Write-Host $server call inside of the if statement, before the call to Compare-Object. That will make it so that your script only outputs the server name if it is in both files and if there is a difference between them.

    by spama at 2012-10-16 07:41:25

    Hello

    thank you for your help, i progress on it but not a lot. :/
    I come back to you when i will have more new code lines and when my script is finished 🙂

    regards

You must be logged in to reply to this topic.