Comparing 2 csv files and exporting difference

This topic contains 9 replies, has 3 voices, and was last updated by  Olaf Soyk 1 year ago.

  • Author
    Posts
  • #54649

    lyn wood
    Participant

    I have been trying to compare 2 csv files and export the difference to a separate csv file and it is not working properly when I test it, it will give result if there is nothing in the Unique ID column but not when the number is different which is what I need it to.

    Heading of CSV files
    First Name, Surname, Unique ID, Tags

    Current Script:

    # Comparing 2 CSV files and exporting difference to a separate file

    $Acer = Import-Csv -Path ".\testb.csv"
    $SAS = Import-Csv ".\testa.csv"

    $arrResult = @()

    foreach ($line in $Acer)
    {
    If (!($SAS | Where-Object {$_."Unique ID" -eq $line."Unique ID"}))
    {

    $arrResult += $line

    }
    }

    $arrResult | Export-Csv ".\NewStudents.csv" -NoTypeInformation

    Thanks for any help.

  • #54650

    Olaf Soyk
    Participant
    Get-Help Compare-Object -Full

    makes the world probably much easier for you

    • This reply was modified 1 year ago by  Olaf Soyk. Reason: correct typo
  • #54670

    lyn wood
    Participant

    Hi Olaf,

    From my testing I have been able to verify what data is being generated from the different parts except from where it gets the data from the $SAS line" If (!($SAS | Where-Object {$_."Unique ID" -eq $line."Unique ID"})) ", from the test results I need to make sure that it is getting the correct data to work. I did try the compare-object but could not work it out. Still very new to this.

    Thanks

    • #54672

      Olaf Soyk
      Participant

      Try this:

      $Acer   = Import-Csv -Path .\testb.csv
      $SAS    = Import-Csv -Path .\testa.csv
      $Header = $Acer | Get-Member | Where-Object -FilterScript {$_.MemberType -eq 'NoteProperty'} | Select-Object -ExpandProperty Name
      Compare-Object -ReferenceObject $Acer -DifferenceObject $SAS -Property 'Unique ID' -PassThru | Select-Object -Property $Header |
      Export-Csv -Path .\NewStudents.csv -NoTypeInformation
  • #54673

    lyn wood
    Participant

    Hi Olaf,

    I tried your script and it did give me different results but not the required end result which was to compare Unique ID from both CSV files and output the difference, So I am learning to understand your script I am presuming that the $_.MemberType cycles through the $Acer file a line at a time or does it search the whole colunm?

    Thanks

    • #54680

      Olaf Soyk
      Participant

      Lyn,

      to play a little with it you could execute it line by line in a console and analyse the output.

      What happens with the first 2 lines is (hopefully) obvious. The third line just extracts the headers from one of your csv files to use it later for the csv export. Otherwise you would have the SideIndicators in your output file.

      To make it more visible how it works you can run the following lines in a console window:

      $Acer   = Import-Csv -Path .\testb.csv
      $SAS    = Import-Csv -Path .\testa.csv
      Compare-Object -ReferenceObject $Acer -DifferenceObject $SAS -Property 'Unique ID' -PassThru -IncludeEqual | Format-Table -AutoSize
      • This reply was modified 1 year ago by  Olaf Soyk. Reason: delete obsolete line
  • #54687

    BIJO DEV
    Participant

    Hello All,

    Please check this one

    1) If you need only the duplicates values then you the below code
    $pro1 = import-csv -path 'D:\test1.csv'
    $pro2 = import-csv -path 'D:\test2.csv'

    compare-object $pro1 $pro2 -property "mention your column header" -includeequal -excludedifferent | export-csv 'D:\test3.csv' -notypeinformation

    Note : make sure that the value of property that mean "mention you column header" header should be same in both the csv

    2) if you don't need duplicated values
    $pro1 = import-csv -path 'D:\test1.csv'
    $pro2 = import-csv -path 'D:\test2.csv'

    compare-object $pro1 $pro2 -property "mention your column header" | export-csv 'D:\test3.csv' -notypeinformation

    • #54688

      Olaf Soyk
      Participant

      @BIJO

      Did you try your code? Did it work as espected?

      if I'm not wrong Lyn asked to get the diferences between the 2 files. So your first example is actually wrong. 😉
      Your second example is almost like my last post except of I will have the complete items with all columns of the csv and you only the SideIndicator and the compared property. Right?

  • #54691

    BIJO DEV
    Participant

    @Olaf

    I ran both the scripts and it is running with ease, I didn't got any kind of error.

    well, when I tried your script it was actually not running from my end, I don't know why. May be I would have
    screwed out somewhere

    Anyways the scripts are running fine for me both of them.

    • #54692

      Olaf Soyk
      Participant

      @BIJO

      to not get any error does not mean the script does what it should! What's in your 'D:\test3.csv' when you had in your csv files what Lyn mentioned in the initial post (Headers: First Name, Surname, Unique ID, Tags)?

You must be logged in to reply to this topic.