CODEWITHSUNDEEP
powershellcsvcompareobject
joostvdlinden
joostvdlinden

Reputation: 55

PowerShell Compare-Object and Export-Csv exporting wrong data

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!

Upvotes: 2

Views: 1230

Answers (1)

Andrew Ryan Davis
Andrew Ryan Davis

Reputation: 659

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

Upvotes: 3

Related Questions