PowerShell Compare-Object and Export-Csv exporting wrong data

Question

Problem

I have two CSV files, file A and file B. Both files contain the same header.
The files contain information about quotes and orders.

File A was created first, at let’s say 10:00 AM. File B was created at 11:00 AM. That’s when the status column value updates from ‘Quote’ to ‘Order’, and maybe some other details as well.

I use Compare-Object and Export-Csv combined to export the differences to a new CSV file, but only the newest (up to date) information should be exported.
The problem is: Compare-Object correctly detects that a specific row has been changed, but instead of using the data from file B, it is using the data from file A (the older version).

Example (file contents)

I have highlighted the fields that have changed in bold.

File A

"CustomerName","Address","Postalcode","City","ReferenceNumber","CustomerNumber","Statuscode","DeliveryWeek","WorkDescription","Status","OrderReference","Advisor"  
"Example Customer","Example Address 1","9999 EX","EXAMPLE CITY","217098","8629",**"Quote"**,**""**,"Example Product","Example Status","Private","Example Advisor"

File B

"CustomerName","Address","Postalcode","City","ReferenceNumber","CustomerNumber","Statuscode","DeliveryWeek","WorkDescription","Status","OrderReference","Advisor"  
"Example Customer","Example Address 1","9999 EX","EXAMPLE CITY","217098","8629",**"Order"**,**"Call-off"**,"Example Product","Example Status","Private","Example Advisor"

Script

OK there are quite some lines there. I’ll share the lines where I believe the issue should reside.

$timestamp = (get-date -UFormat "%A %d-%m-%Y %R" | ForEach-Object { $_ -replace ":", "-" })
$prefix="Export-"
$exportlocation = "C:\Users\username\Desktop\UTF8-format\" 
$ExportChangesFolder = "C:\Users\username\Desktop\Changes\"

$PreviousCSV = Import-Csv $PreviousFile -Header "CustomerName","Address","Postalcode","City","ReferenceNumber","CustomerNumber","Statuscode","DeliveryWeek","WorkDescription","Status","OrderReference","Advisor"
$NewCSV = Import-Csv $exportlocation$prefix$timestamp".csv" -Header "CustomerName","Address","Postalcode","City","ReferenceNumber","CustomerNumber","Statuscode","DeliveryWeek","WorkDescription","Status","OrderReference","Advisor"

$propsToCompare = $PreviousCSV[0].psobject.properties.name
Compare-Object -ReferenceObject $PreviousCSV -DifferenceObject $NewCSV -Property $propsToCompare -PassThru | select $propsToCompare | sort -Unique -Property "ReferenceNumber" | Select-Object * -ExcludeProperty SideIndicator | Export-Csv $ExportChangesFolder$prefix$timestamp".csv" -NoTypeInformation

Normally, all file names are populated automatically, as this is a recurring task setup using Windows Task Scheduler. During troubleshooting I have manually filled in the file names where the variables are declared. And everytime I run it manually, it works fine!

Andrew Ryan Davis · Accepted Answer · 2021-01-10T05:59:30.877

I think what you may be missing is the SideIndicator. You should be able to just choose the list of SideIndicators you want with "<=" being the things that exist only in the left csv and "=>" being the things that only exist in the right.

It looks like you are also specifying headers and then grabbing the headers from the csv, but you mentioned they have the same headers?

The Get-Date at runtime targeting an existing file for Import-Csv is also a bit confusing, but I'm guessing there's more to the script that builds this csv before it's imported and Get-Date runs.

Here's something that is working on my end:

$timestamp = ((get-date -UFormat "%A %d-%m-%Y %R") -replace ":", "-")
$prefix="Export-"
$exportLocation = "C:\Users\username\Desktop\UTF8-format\" 
$exportChangesFolder = "C:\Users\username\Desktop\Changes\"

$headers = $previousCSV[0].psobject.properties.name

$previousCSV = Import-Csv $previousFile
$newCSV = Import-Csv $exportLocation$prefix$timestamp".csv"

$compareParams = @{
    ReferenceObject  = $previousCSV
    DifferenceObject = $newCSV
    Property         = $headers
    PassThru         = $true
}

Compare-Object @compareParams |
    Where-Object {$_.SideIndicator -eq "=>"} |
    Select-Object $headers | 
    Sort-Object -Unique -Property "ReferenceNumber" | 
    Select-Object * -ExcludeProperty SideIndicator |
    Export-Csv $exportChangesFolder$prefix$timestamp".csv" -NoTypeInformation

Hi Andrew, thanks! I took a closer look at how to use SideIndicator, it appears that when I include {$_.SideIndicator -eq "=>"}, it would only pass information when the ReferenceNumber exists only in File B. In my case the ReferenceNumber will be there in both files, but the Status value will be updated in B. Am I correct that therefore using {$_.SideIndicator -eq "=>"} will not lead to the desired outcome? "... a property value appeared only in the reference object (<=) or only in the difference object (=>). If the IncludeEqual parameter is used, (==) indicates the value is in both objects." — joostvdlinden, Jan 11 '21 at 10:50
I think I got it right this time! I'm quite new to this Compare-Object stuff. Because we include Select-Object * it treats all the properties as a whole (object) right? The missing piece to the puzzle was obviously the SideIndicator as you suggested. Thanks for the help, it was really clarifying. Now I also see that some parts of my code were really obsolete and/or unnessecary. — joostvdlinden, Jan 11 '21 at 15:12

PowerShell Compare-Object and Export-Csv exporting wrong data

Problem

Example (file contents)

Script

1 Answers1