Compare 2 csv files

PowerShell Help
1

I have tried many approaches to this and everyone so far seems to be line by line based rather than data based. This is fine if there was only a new add because 1 file will have 1 extra line. If I add that new line to the middle of the file it will consider it and everything after it new.

What I am trying to do is take a csv file that is generated by a separate program and compare it to the previous days file. I am looking for any new data, modified data or deleted data only. I do have a unique column in the csv named User ID if that helps. Here are a few examples of what I am looking for:

This would generate an empty csv3

csv1:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222

csv2:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222

csv3:

User ID,Name,Phone

This would generate an add

csv1:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222

csv2:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111

csv3:

User ID,Name,Phone,Action(new column)
55,Mark,2222222222,add

This would generate a modification

csv1:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222

csv2:

User ID,Name,Phone
1,John,1234567890
2,Max,0987654321
50,Mary,1111111111

csv3:

User ID,Name,Phone,Action(new column)
2,Max,0987654321,old
2,Jane,0987654321,new

This would generate a delete

csv1:

User ID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222

csv2:

User ID,Name,Phone
1,John,1234567890
2,Max,0987654321
3,Jim,3333333333
50,Mary,1111111111

csv3:

User ID,Name,Phone,Action(new column)
3,Jim,3333333333,delete

Here is the script that gets me the closest it will allow me to compare but only if the new line is at the end of csv.

$path = “c:\ *.csv”

$files = Get-ChildItem -Path $path |
Sort-Object LastWriteTime -Descending

$file1a = $files | Select-Object -First 1
$file2a = $files | Select-Object -First 1 -Skip 1

$file1 = Import-CSV -Path $file1a
$file2 = Import-CSV -Path $file2a

Compare-Object $file2 $file1 -PassThru | Export-CSV “c:\compare.csv” -NoTypeInformation

2

Actually I’m pretty confused by your data samples. Could you please show the two input tables you want to compare and the result you expoect after the comparison?

I’d expect something similar to the following may work …

$CSV01 = Import-Csv -Path .\file01.csv -Delimiter ','
$CSV02 = Import-Csv -Path .\file02.csv -Delimiter ','

Compare-Object -ReferenceObject $CSV01 -DifferenceObject $CSV02 -PassThru
3

I edited and tried to make it clearer.

4

This only works if the new line is at the end of the csv. If the new line was added before the end then the result would show the original end line as the result. I think it is only doing a count.

5

I’m not sure if I missunderstand you or if you missunderstand the cmdlet. :wink:

1st example:

$CSV1 = 
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222
'@ | ConvertFrom-Csv

$CSV2 = 
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222
'@ | ConvertFrom-Csv

Compare-Object -ReferenceObject $CSV1 -DifferenceObject $CSV2 -Property UserID, Name, Phone -PassThru

what’s not generating any output because they are the same.

2nd example:

$CSV1 =
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222
'@ | ConvertFrom-Csv

$CSV2 =
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
'@ | ConvertFrom-Csv

Compare-Object -ReferenceObject $CSV1 -DifferenceObject $CSV2 -Property UserID, Name, Phone -PassThru

… what outputs this:

UserID Name Phone      SideIndicator
------ ---- -----      -------------
55     Mark 2222222222 <=

… 3rd example:

$CSV1 =
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222
'@ | ConvertFrom-Csv

$CSV2 =
@'
UserID,Name,Phone
1,John,1234567890
2,Max,0987654321
50,Mary,1111111111
'@ | ConvertFrom-Csv

Compare-Object -ReferenceObject $CSV1 -DifferenceObject $CSV2 -Property UserID, Name, Phone -PassThru

what outputs …

UserID Name Phone      SideIndicator
------ ---- -----      -------------
2      Max  0987654321 =>
2      Jane 0987654321 <=
55     Mark 2222222222 <=

… and finally the 4th example:

$CSV1 =
@'
UserID,Name,Phone
1,John,1234567890
2,Jane,0987654321
50,Mary,1111111111
55,Mark,2222222222
'@ | ConvertFrom-Csv

$CSV2 =
@'
UserID,Name,Phone
1,John,1234567890
2,Max,0987654321
3,Jim,3333333333
50,Mary,1111111111
'@ | ConvertFrom-Csv

Compare-Object -ReferenceObject $CSV1 -DifferenceObject $CSV2 -Property UserID, Name, Phone -PassThru

… and the result is this output:

UserID Name Phone      SideIndicator
------ ---- -----      -------------
2      Max  0987654321 =>
3      Jim  3333333333 =>
2      Jane 0987654321 <=
55     Mark 2222222222 <=

I think you just have to evaluate the side indicator … or do I still missunderstand it?

6

Thank you. That way seems to work. I will test it to make sure fully. Seems the -property is what I was missing.

7

Great. I’m glad if it helped. :+1:t4: :wink: :slightly_smiling_face: