locked
CSV compare of 2 files with powershell RRS feed

  • Question

  • I have 2 csv file that contain 6 columns each.  The header is the same in both. But one csv file contains more lines than the other.  One named Payroll.csv and the other Salesman.csv. The Payroll export contains more users than the Salesman csv file.

    example

    Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID

    I need to be able to compare and match based on the EmployeeID. Which I can do but Once I find a match in Salesman file I need to compare to make sure that the other columns in Salesman do match the columns for that EmployeeID in the Payroll file.  If an EmployeeID is found to have a mismatch of data I need to export it out to another file for the Personel dept to look at in the morning.  It would be great to have a new column added to the mismatch file that would say the variable name that did not match, but Im not sure how to start on this one.  I can do basic compares in powershell such as if the EmployeeID is missing export but this is much more complicated.  Can anyone help send me in the right direction?

    Thank you for your help!

    Saturday, May 2, 2015 12:23 AM

Answers

  • This creates two sample CSV files and compares them, errors are output to a third file. Not all errors are checked but hope it is a good starting point.

    $PayRollCSV="c:\Payroll.csv"
    $SalesManCSV="c:\Salesman.csv"
    $ErrorsCSV="c:\Errors.csv"
    
    Add-Content $PayRollCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID"
    Add-Content $PayRollCSV "Smith,John,Fred,1986,12345,E01"
    Add-Content $PayRollCSV "Doe,John,,1985,12346,E02"
    Add-Content $PayRollCSV "Public,John,Q,1984,12347,E03"
    Add-Content $PayrollCSV "Doe,Jane,,1983,12347,E04"
    
    Add-Content $SalesManCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID"
    Add-Content $SalesManCSV "Smithy,John,Fred,1986,12345,E01"
    Add-Content $SalesManCSV "Doe,Johnny,,1985,12346,E02"
    Add-Content $SalesManCSV "Public,John,,1984,12347,E03"
    Add-Content $SalesManCSV "Doe,Jane,,1983,12347,E04"
    Add-Content $SalesManCSV "Gates,Bill,,1982,12348,E05"
    
    Add-Content $ErrorsCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID,Error"
    
    # Build Payroll Hashtable
    
    $PayRollFile=Import-CSV $PayRollCSV
    $PayRollHashTable = @{}
    ForEach ($Line in $PayRollFile)
    {
        $PayRollLine=$PayRollHashTable[$Line.EmployeeID]
        If ($PayRollLine -EQ $Null)
        {
            $PayRollHashTable.Add($Line.EmployeeID,$Line)
        }
    }
    
    # Compare SalesMan against Payroll Hashtable
    
    $SalesManFile=Import-CSV $SalesManCSV
    ForEach ($SalesManLine in $SalesManFile)
    {
        $IsError = $True
        $PayRollLine=$PayRollHashTable[$SalesManLine.EmployeeID]
        If ($PayRollLine -EQ $Null)
        {
            $LineError = "Does not exist"      
        }
        ElseIf ($SalesManLine.LastName -NE $PayRollLine.LastName)
        {
            $LineError = "LastName Does Not Match"      
        }
        ElseIf ($SalesManLine.FirstName -NE $PayRollLine.FirstName)
        {
            $LineError = "FirstName Does Not Match"      
        }
        ElseIf ($SalesManLine.MiddleName -NE $PayRollLine.MiddleName)
        {
            $LineError = "MiddleName Does Not Match"      
        }
        Else
        {
           $IsError = $False
        }
        IF ($IsError)
        {
           $ThisError = "$($SalesManLine.LastName),$($SalesManLine.FirstName),$($SalesManLine.MiddleName),$($SalesManLine.Birthday),$($SalesManLine.Licencenumber),$($SalesManLine.EmployeeID),$LineError"
           Add-Content $ErrorsCSV $ThisError
        }
    }
    
    Get-Content $ErrorsCSV

    Gives this output

    Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID,Error
    Smithy,John,Fred,1986,12345,E01,LastName Does Not Match
    Doe,Johnny,,1985,12346,E02,FirstName Does Not Match
    Public,John,,1984,12347,E03,MiddleName Does Not Match
    Gates,Bill,,1982,12348,E05,Does not exist


    • Edited by Brian Nadjiwon Saturday, May 2, 2015 8:16 AM
    • Proposed as answer by AnnaWY Wednesday, May 20, 2015 9:01 AM
    • Marked as answer by AnnaWY Thursday, May 21, 2015 2:42 AM
    Saturday, May 2, 2015 8:13 AM

All replies

  • Can you post your script with a specific question and any error messages.


    \_(ツ)_/

    Saturday, May 2, 2015 12:41 AM
  • This creates two sample CSV files and compares them, errors are output to a third file. Not all errors are checked but hope it is a good starting point.

    $PayRollCSV="c:\Payroll.csv"
    $SalesManCSV="c:\Salesman.csv"
    $ErrorsCSV="c:\Errors.csv"
    
    Add-Content $PayRollCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID"
    Add-Content $PayRollCSV "Smith,John,Fred,1986,12345,E01"
    Add-Content $PayRollCSV "Doe,John,,1985,12346,E02"
    Add-Content $PayRollCSV "Public,John,Q,1984,12347,E03"
    Add-Content $PayrollCSV "Doe,Jane,,1983,12347,E04"
    
    Add-Content $SalesManCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID"
    Add-Content $SalesManCSV "Smithy,John,Fred,1986,12345,E01"
    Add-Content $SalesManCSV "Doe,Johnny,,1985,12346,E02"
    Add-Content $SalesManCSV "Public,John,,1984,12347,E03"
    Add-Content $SalesManCSV "Doe,Jane,,1983,12347,E04"
    Add-Content $SalesManCSV "Gates,Bill,,1982,12348,E05"
    
    Add-Content $ErrorsCSV "Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID,Error"
    
    # Build Payroll Hashtable
    
    $PayRollFile=Import-CSV $PayRollCSV
    $PayRollHashTable = @{}
    ForEach ($Line in $PayRollFile)
    {
        $PayRollLine=$PayRollHashTable[$Line.EmployeeID]
        If ($PayRollLine -EQ $Null)
        {
            $PayRollHashTable.Add($Line.EmployeeID,$Line)
        }
    }
    
    # Compare SalesMan against Payroll Hashtable
    
    $SalesManFile=Import-CSV $SalesManCSV
    ForEach ($SalesManLine in $SalesManFile)
    {
        $IsError = $True
        $PayRollLine=$PayRollHashTable[$SalesManLine.EmployeeID]
        If ($PayRollLine -EQ $Null)
        {
            $LineError = "Does not exist"      
        }
        ElseIf ($SalesManLine.LastName -NE $PayRollLine.LastName)
        {
            $LineError = "LastName Does Not Match"      
        }
        ElseIf ($SalesManLine.FirstName -NE $PayRollLine.FirstName)
        {
            $LineError = "FirstName Does Not Match"      
        }
        ElseIf ($SalesManLine.MiddleName -NE $PayRollLine.MiddleName)
        {
            $LineError = "MiddleName Does Not Match"      
        }
        Else
        {
           $IsError = $False
        }
        IF ($IsError)
        {
           $ThisError = "$($SalesManLine.LastName),$($SalesManLine.FirstName),$($SalesManLine.MiddleName),$($SalesManLine.Birthday),$($SalesManLine.Licencenumber),$($SalesManLine.EmployeeID),$LineError"
           Add-Content $ErrorsCSV $ThisError
        }
    }
    
    Get-Content $ErrorsCSV

    Gives this output

    Lastname,Firstname,MiddleName,Birthday,Licencenumber,EmployeeID,Error
    Smithy,John,Fred,1986,12345,E01,LastName Does Not Match
    Doe,Johnny,,1985,12346,E02,FirstName Does Not Match
    Public,John,,1984,12347,E03,MiddleName Does Not Match
    Gates,Bill,,1982,12348,E05,Does not exist


    • Edited by Brian Nadjiwon Saturday, May 2, 2015 8:16 AM
    • Proposed as answer by AnnaWY Wednesday, May 20, 2015 9:01 AM
    • Marked as answer by AnnaWY Thursday, May 21, 2015 2:42 AM
    Saturday, May 2, 2015 8:13 AM
  • Sorry for the late reply. Ive been on vacation. That is a great start. I see where you are coming from now.. I will have to add some more categories, but you hit the nail on the head. Thank you!
    Thursday, May 7, 2015 11:21 PM