Compare excel

This topic contains 15 replies, has 4 voices, and was last updated by Profile photo of Brad Brad 5 months, 2 weeks ago.

Viewing 15 posts - 1 through 15 (of 16 total)
  • Author
    Posts
  • #37488
    Profile photo of Brad
    Brad
    Participant

    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.

    #37492
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

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

    #37498
    Profile photo of Richard Diphoorn
    Richard Diphoorn
    Participant

    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. 🙂

    #37512
    Profile photo of Brad
    Brad
    Participant

    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.

    #37526
    Profile photo of Brad
    Brad
    Participant

    I try

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

    but it not work

    #37531
    Profile photo of Richard Diphoorn
    Richard Diphoorn
    Participant

    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!

#37557
Profile photo of Brad
Brad
Participant

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

#37559
Profile photo of Curtis Smith
Curtis Smith
Participant

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.

#37583
Profile photo of Brad
Brad
Participant

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.

#37605
Profile photo of Curtis Smith
Curtis Smith
Participant

Brad, provide a sample of your current output.

#37627
Profile photo of Max Kozlov
Max Kozlov
Participant

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
#37790
Profile photo of Brad
Brad
Participant

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

#37839
Profile photo of Max Kozlov
Max Kozlov
Participant

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 ? 🙂

#37886
Profile photo of Brad
Brad
Participant

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 ? :)

#37924
Profile photo of Max Kozlov
Max Kozlov
Participant

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic.