Compare two CSV files via PowerShell

Hello. Recently I had a task to find a way how to compare two CSV files, find a match and save it in a new CSV file.

I was struggling a lot, but with a help of a friend, we were able to create such a script. Thank you Michal 🙂

Here, I will show you how to compare two CSV files by taking one column from each file, find a match, and save it into a new CSV file.

I have two CSV files:

OrdersA.csv and OrdersB.csv

In OrdersA, I have some orders which have been processed. In OrdersB, I have some orders as well, but maybe few of them are missing from OrdersA, and couple are new, which are not in OrdersA.

Now, I would like to compare both files, find a match, save it into a new CSV file. First, lets take a look how our files look like:

This is OrdersA document, and in this screenshot you can see few columns, the main one which we will focus on is ‘Item Number’.

Lets take a look at OrdersB document:

We will focus on ‘Number’ column, which we have to compare with ‘Item Number’ column from OrdersA CSV file. As you may see, ‘Number’ column is missing letter ‘A’, so we will have to find a way how to compare these two columns by excluding letter ‘A’.

OK, first, lets import these two files:

$OrdersA = Import-CSV -Path .\OrdersA.csv
$OrdersB = Import-CSV -Path .\OrdersB.csv

As you may see, I am using relative paths, which means that in order for script to find these files, you have to enter with PowerShell console in folder where the files are located.

For example, files are located in “X:\Study Materials\Scripts\Get-Report\Get-Report\For-Site”. So instead of writing this path, I will use a relative path, but when I open PowerShell console, I would need to

cd "X:\Study Materials\Scripts\Get-Report\Get-Report\For-Site"

I would also like to see how long it will take for script to finish, so I will define a start variable.

And lets put there a counter, so we can see how many matches the script finds:

$matchcounter
$start = [system.datetime]::Now

Now it is the fun part. Lets start with the actual script shall we?

In order to compare both columns, we need to use a nested foreach. It will check each cell if there is a match, and if there is one, it will export the results to a new CSV file.

foreach ($order1 in $OrdersA){
    $matched = $false
    foreach ($order2 in $OrdersB){

I am going to create a new object for each match script returns. Also, since the ‘Item Number’ column contains letter “A”, I will remove it.

$obj = "" | select "Item Number","Location","Responsible Manager","Status","Sub Status","Open Date","Date Notified","Target Date","Due in Week","Severity"
if(($order1.'Item Number' -replace "A" ) -eq $order2.'Number' ){
    $matchCounter++
    $matched = $true
    $obj.'Item Number' = $order1.'Item Number'
    $obj.'Location' = $order1.'Location'
    $obj.'Responsible Manager' = $order1.'Responsible Manager'
    $obj.Status = $order1.Status
    $obj."Sub Status" = $order1."Sub Status"
    $DueInWeek = $order2.'Due in Week'
    $obj.'Open Date' = $order1.'Open Date'
    $TargetDate = $order1.'Target Date'
    $obj.'Target Date' = $TargetDate

When it is finished with one item, it will post a message that it found one order and continue.

Write-Host "Match Found Orders " "$matchCounter"

In the end, the match will be saved into a new CSV file, it will Append the data (we want all the matched data, if you do not put -Append, it will keep only one row in CSV).

-NoTypeInformation is useful because it will not save into CSV data type at the beginning.

$obj | Export-Csv -Path .\report\Report_Orders.csv -Append -NoTypeInformation}}}

Here is the whole script:

cls
$OrdersA = Import-CSV -Path .\OrdersA.csv
$OrdersB = Import-CSV -Path .\OrdersB.csvRemove-Item -path .\report\*.csv
$matchcounter = 0
$start = [system.datetime]::Now
foreach ($order1 in $OrdersA){
    $matched = $false
    foreach ($order2 in $OrdersB){
        $obj = "" | select "Item Number","Location","Responsible Manager","Status","Sub Status","Open Date","Date Notified","Target Date","Due in Week","Severity"
        if(($order1.'Item Number' -replace "A" ) -eq $order2.'Number' ){
            $matchCounter++
            $matched = $true
            $obj.'Item Number' = $order1.'Item Number'
            $obj.'Location' = $order1.'Location'
            $obj.'Responsible Manager' = $order1.'Responsible Manager'
            $obj.Status = $order1.Status
            $obj."Sub Status" = $order1."Sub Status"
            $DueInWeek = $order2.'Due in Week'
            $obj.'Open Date' = $order1.'Open Date'
            $TargetDate = ($order1.'Target Date' -split " ")[0]
            $obj.'Target Date' = $TargetDate

            Write-Host "Match Found Orders " "$matchCounter"
            $obj | Export-Csv -Path .\report\Report_Orders.csv -Append -NoTypeInformation
        }
    }
}
$end = [system.datetime]::Now
$resultTime = $end - $start
Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."

This is how saved output looks like:

Share:

Related