Advanced comparaison for 2 csv

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


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 …

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

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