Author Posts

April 10, 2016 at 10:58 am

Hi,
I have two excel file(one master and second Test)
I try compare 1st column in both file and show(I need highlight) different in second file
my code work and create 3rd separate file, but I need highlight different in Test file

$csvMaster = Import-Csv -path "C:\master.csv"
$csvTest = Import-Csv -path "C:\test.csv"
$comparecolumn = 'Name'

$dupl = Compare-Object $csvMaster  $csvTest  -property $comparecolumn  -includeEqual -ExcludeDifferent -PassThru | Select-Object -ExpandProperty $comparecolumn 

Where-Object {$_.$comparecolumn -notin $dupl } 
| Export-CSV

Thx.

April 10, 2016 at 8:09 pm

If you need to do highlighting, you can't use a CSV file. CSV does not support formatting.

April 11, 2016 at 3:13 am

If you want to do some more advanced stuff in Excel, I recommend you to look into the Open XML PowerTools. Those Cmdlets are ment for manipulating Open XML docs (*.docx, *.xslx, etc.). https://github.com/OfficeDev/Open-Xml-PowerTools

If you combine it with your script, you can do some pretty cool stuff. 🙂

April 11, 2016 at 7:51 am

Hi Curtis and Richard

Thank you for your help and notes
to bad I can't change color for row in csv file, and to bad I can't use this tools

I even change my script a bit

$csvMaster = Import-Csv -path "C:\master.csv"
$csvTest = Import-Csv -path "C:\test.csv"
$comparecolumn = 'Name'

$include = Compare-Object $csvMaster $csvTest -Property $comparecolumn -IncludeEqual -PassThru

$Exclude = Compare-Object $csvMaster $csvTest -Property $comparecolumn -ExcludeDifferent -PassThru

$compare = $include + $Exclude |
Export-Csv -Path "C:\TEST.csv" -NoTypeInformation

and it give me column with SideIndicator

if I can't highlighting, how I can change indicator with some name(Exemple: => change with Master)
Thanks for help.

April 11, 2016 at 10:45 am

I try

$include = Compare-Object $csvMaster $csvTest -Property $comparecolumn -IncludeEqual -PassThru |
 ?{ $_.SideIndicator -eq '=>' | Select-Object @{ expression={$_.InputObject}; label='Master' }}

but it not work

April 11, 2016 at 12:26 pm

I think you are not completely aware how Compare-Object works. Let me tyr to explain.

The Cmdlet compares two objects, side to side, and it will show you the difference with a side indicator.

For example, I import the contents of 2 csv files in a variable each. But I expand the property called 'Name'. Why? Because the 'Name' property is actually an array.
Here I'm importing the csv file in a variable, without expanding the property:

$1 = Import-Csv C:\temp\1.csv

Then I look up the property type:

$1.GetType()

This will give me this result:

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

And when you then compare two objects of the same kind with Compare-Object, this result is being shown:

PS C:\> $1 = Import-Csv C:\temp\1.csv
PS C:\> $2 = Import-Csv C:\temp\2.csv
PS C:\> Compare-Object -ReferenceObject $1 -DifferenceObject $2

InputObject SideIndicator
----------- -------------
@{Name=6}   =>

You can see the difference; the object is present in variable $1, but not variable $2. But it's not very useful, and you can't use this to export the differences to a separate csv file. Look at this:

PS C:\> Compare-Object -ReferenceObject $1 -DifferenceObject $2 | Select-Object InputObject

InputObject
-----------
@{Name=6}

And this is what you will see when you try to export to a difference file:

PS C:\> Compare-Object -ReferenceObject $1 -DifferenceObject $2 | Select-Object InputObject | Export-Csv -Path C:\Temp\difference.csv -NoTypeInformation
PS C:\> Import-Csv -Path C:\Temp\difference.csv

InputObject
-----------
@{Name=6}

So how do you handle this? With expanding the object property 'Name'. You do this with Select-Object -ExpandProperty:

$1 = Import-Csv C:\temp\1.csv | Select-Object -ExpandProperty Name
$2 = Import-Csv C:\temp\2.csv | Select-Object -ExpandProperty Name

Next we can check the differences, and the output is much more usable:

PS C:\> Compare-Object -ReferenceObject $1 -DifferenceObject $2

InputObject SideIndicator
----------- -------------
6           =>

Now we export it again to a separate csv file:

Compare-Object -ReferenceObject $1 -DifferenceObject $2 | Select-Object InputObject | Export-Csv -Path C:\Temp\difference.csv -NoTypeInformation

And when we check the file, you have the difference in a readable format:

PS C:\> Import-Csv -Path C:\Temp\difference.csv

InputObject
-----------
6

So the complete solution would be:

$1 = Import-Csv C:\temp\1.csv | Select-Object -ExpandProperty Name
$2 = Import-Csv C:\temp\2.csv | Select-Object -ExpandProperty Name
Compare-Object -ReferenceObject $1 -DifferenceObject $2 | Select-Object InputObject | Export-Csv -Path C:\Temp\difference.csv -NoTypeInformation

Or you can also do it in a oneliner:

Compare-Object -ReferenceObject (Import-Csv C:\temp\1.csv | Select-Object -ExpandProperty Name) -DifferenceObject (Import-Csv C:\temp\2.csv | Select-Object -ExpandProperty Name) | Select-Object InputObject | Export-Csv -Path C:\Temp\difference.csv -NoTypeInformation

Just try to remember, Compare-Object compares every property in the reference object with the difference object. If anything is different, it will show you the side indicator:

    => means property is present in reference object, but not in difference object
    < = means property is present in difference object, but not in reference object == means property is present in both objects (this you would only see when using the parameter -IncludeEqual.

Hope this helps!

April 12, 2016 at 4:21 am

Thank you Richard
for complete explanation
much appreciated

how I can replace side indicator(== ) with some name
I want replace == with Default, with User

again Thanks for good example

April 12, 2016 at 4:32 am

Here is an example using Select-Obect

$1 = @('a','b','c','e','f')
$2 = @(1,'b',2,'c',3,4)
Compare-Object -ReferenceObject $1 -DifferenceObject $2 -IncludeEqual | Select-Object InputObject, @{Label = 'SideIndicator'; Expression = {If($_.SideIndicator -eq "=="){'Default'}else{$_.SideIndicator}}}

Results:

InputObject SideIndicator
----------- -------------
b           Default      
c           Default      
1           =>           
2           =>           
3           =>           
4           =>           
a           < =           
e           <=           
f           <=           

FYI, the space between the < and the = on the "a" line is a formatting issue with the forum. That does not happen in the actual output.

April 12, 2016 at 10:39 am

Hi Curtis,
thanks for this example
this code

$csvMaster = Import-Csv -path "C:\master.csv"
$csvTest = Import-Csv -path "C:\test.csv"
$comparecolumn = 'Name'

$include = Compare-Object $csvMaster $csvTest -Property $comparecolumn -IncludeEqual -PassThru

$Exclude = Compare-Object $csvMaster $csvTest -Property $comparecolumn -ExcludeDifferent -PassThru

$compare = $include + $Exclude |
Export-Csv -Path "C:\TEST.csv" -NoTypeInformation

work for me.
It create file with data and also add different from both files
what I need, it just small touch, how change SideIndicator on names
Thanks.

April 12, 2016 at 2:53 pm

Brad, provide a sample of your current output.

April 13, 2016 at 12:23 am

Use Hashtables:

$Indicator = @{ '=>' = 'Right'; '< =' = 'Left'; '==' = 'Same' }
$1 = @('a','b','c','e','f')
$2 = @(1,'b',2,'c',3,4)
Compare-Object -ReferenceObject $1 -DifferenceObject $2 -IncludeEqual |
Select-Object InputObject, @{Label = 'SideIndicator'; Expression = { $Indicator[$_.SideIndicator] } }

InputObject SideIndicator
----------- -------------
b           Same
c           Same
1           Right
2           Right
3           Right
4           Right
a           Left
e           Left
f           Left

April 14, 2016 at 12:22 pm

Hi Curtis,
this is my output
Name Version SideIndicator
a 1 ==
b 2 ==
c 3
and if I use hash, it not work for me

April 15, 2016 at 5:30 am

Compare-Object -Excludedifferent out nothing because you don't use -IncludeEqual

you don't need two comparations
only
Compare-Object -IncludeEqual

What I'm doing wrong ? 🙂

April 16, 2016 at 7:25 am

Thank you Max,
I try

$Indicator = @{ '=>' = 'Right'; '< =' = 'Left'; '==' = 'Same' } $t1 = "C:\Master.csv" $t2 = "C:\Test.csv" $comparecolumn = 'Name' Compare-Object $t1 $t2 -Property $comparecolumn -IncludeEqual -PassThru | Select-Object *, @{Label = 'Side'; Expression = { $Indicator[$_.SideIndicator] } } | Export-Csv -Path "C:\Test.csv" -NoTypeInformation
and result

SideIndicator Length Side
== 29 Same

What I'm doing wrong ? :)

April 17, 2016 at 11:37 pm

You are doing wrong many things 🙂

1. You do not import your data – You comparing not data but filenames 🙂
change
$t1 = "C:\Master.csv" to $t1 = Import-csv -Path "C:\Master.csv" -Delimiter "your csv delimiter here"
and the same for $t2

2. Export your result to different file, not the same as import
Export-Csv -Path "C:\Test.csv"` to `Export-Csv -Path "C:\TestOutput.csv"
because of buffering it can work for small file but can have many errors with big (may be your problem lies here, because in previous examples you really import data but use the same output csv as input)

3. Look carefully to 'Left' Indicator because of forum "feature" I don't know is you have a space between "<" and "=" – there must be no space

April 19, 2016 at 8:26 am

Thank you Max, Curtis and Richard
it work now, even if I am re-write on the same file
Thanks