Author Posts

September 27, 2016 at 8:19 am

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.

September 27, 2016 at 8:22 am

Get-Help Compare-Object -Full

makes the world probably much easier for you

  • This reply was modified 1 year, 12 months ago by  Olaf Soyk. Reason: correct typo

September 28, 2016 at 12:45 am

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

September 28, 2016 at 1:05 am

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

September 28, 2016 at 2:19 am

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

September 28, 2016 at 8:00 am

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, 12 months ago by  Olaf Soyk. Reason: delete obsolete line

September 28, 2016 at 1:47 pm

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

September 28, 2016 at 2:01 pm

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

September 28, 2016 at 2:31 pm

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

September 28, 2016 at 2:36 pm

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