CODEWITHSUNDEEP
powershell
Naveen
Naveen

Reputation: 322

Compare two csv files and find discrepancies

I am new in powershell world , I got some project on powershell for inventory reconciling .

I am not sure how to proceed on this , I tried some basic steps and I am able to export

users/group/group membership.

Following are the requirements :

AD query:

Get-QADUser -searchRoot $OuDomain -SizeLimit 0 |
Select-Object dn, sAMAccountName, @{Name="Groups";Expression={(Get-QADMemberOf $_ | Select-Object -expandProperty Name) -join ";"}} | 
Sort-Object SamAccountName | 
export-csv $FilePath

I have now two csv files likes AD_users.csv and Oracle_users.csv

I want to compare both files and redirect the difference like

AD users does not exist in Oracle Oracle User does not exist in AD

Sample data

AD_users.csv

u388848993
K847447388
u994888484

Oracle_users.csv

k095848889
u388848993

I can query oracle database , AD query is also fine the only concern is that I am not able to compare the output.

Upvotes: 2

Views: 2926

Answers (1)

Sverker84
Sverker84

Reputation: 465

I did it something like this in a script I wrote:

[System.Collections.ArrayList]$adlist = Get-Content c:\users\sverker\desktop\ad.csv |Sort-Object
[System.Collections.ArrayList]$oraclelist = Get-Content c:\users\sverker\desktop\oracle.csv |Sort-Object
$Matching_numbers = @()

ForEach ($number in $adlist)
{
  if ($oraclelist.Contains($number))
  {
        $Matching_numbers += $number
  }
}
ForEach ($number in $Matching_numbers)
{
  $adlist.Remove($number)
  $oraclelist.Remove($number)
}

now $Matching_numbers now contains the matching numbers and $adlist contains only numbers from AD and $oraclelist only numbers from Oracle

you can then loop through the list and display values:

Write-Host "Matches:"
ForEach ($value in $Matching_numbers)
{
  $Message += $value + [Environment]::NewLine
}
Write-Host $Message

Write-Host "AD only:"
ForEach ($value in $adlist)
{
  $MessageAd += $value + [Environment]::NewLine
}
Write-Host $MessageAd

Write-Host "Oracle only:"
ForEach ($value in $oraclelist)
{
  $MessageOracle += $value + [Environment]::NewLine
}
Write-Host $MessageOracle

or simply by writing

$Matching_numbers

will output the list to console

You can output the $Message variables to a file or so..

No doubt, there is a nicer way to do it, but this worked for me for a certain type of file.

Upvotes: 4

Related Questions