compare two csv using powershell and return matching and non-matching values

Question

I have two csv files, i want to check the users in username.csv matches with userdata.csv copy
to output.csv. If it does not match return the name alone in the output.csv

For Ex: User Data contains 3 columns

UserName,column1,column2
Hari,abc,123
Raj,bca,789
Max,ghi,123
Arul,987,thr
Prasad,bxa,324

username.csv contains usernames

Hari
Rajesh

Output.csv should contain

Hari,abc,123
Rajesh,NA,NA

How to achieve this. Thanks

Sorry for that.

$Path = "C:\PowerShell"
$UserList = Import-Csv -Path "$($path)\UserName.csv"
$UserData = Import-Csv -Path "$($path)\UserData.csv"

foreach ($User in $UserList)
{
    ForEach ($Data in $UserData)
    {
        If($User.Username -eq $Data.UserName)
        {
            # Process the data

            $Data
        }
    }
}

This returns only matching values. I also need to add the non-matching values in output file. Thanks.

Read help about the if statement.

vonPryz
– vonPryz

2014-09-02 10:14:15 +00:00
Commented Sep 2, 2014 at 10:14 — vonPryz
– vonPryz, Commented Sep 2, 2014 at 10:14

Dane Boulton · Accepted Answer · 2014-09-02 17:58:38Z

4

something like this will work:

$Path = "C:\PowerShell"
$UserList = Import-Csv -Path "$($path)\UserName.csv"
$UserData = Import-Csv -Path "$($path)\UserData.csv"
$UserOutput = @()

    ForEach ($name in $UserList)
    {

        $userMatch = $UserData | where {$_.UserName -eq $name.usernames}
        If($userMatch)
        {
            # Process the data

            $UserOutput += New-Object PsObject -Property @{UserName =$name.usernames;column1 =$userMatch.column1;column2 =$userMatch.column2}
        }
        else
        {
        $UserOutput += New-Object PsObject -Property @{UserName =$name.usernames;column1 ="NA";column2 ="NA"}
        }
    }
$UserOutput | ft

It loops through each name in the user list. Line 9 does a search of the userdata CSV for a matching user name if it finds it it adds the user data for that user to the output if no match is found it adds the user name to the output with NA in both columns.

had to change your userList csv:

usernames
Hari
Rajesh

expected output:

UserName                          column1                           column2                         
--------                          -------                           -------                         
Hari                              abc                               123                             
Rajesh                            NA                                NA

answered Sep 2, 2014 at 17:58

Dane Boulton

1,3251 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dilly B Over a year ago

Hi Dane, Thanks for the script it works perfectly. But why the if and elseif is not working in the script which i posted.It either returns names in multiple times or the length. Thanks for your time again.

Dane Boulton Over a year ago

your method requries you to check each username and do something each time if there is a match or not. The one I posted for each user in the list it does one full loop through the data csv. then does only one check was the user found in the loop or not and process accordingly. Its just more efficient

mortenma71 · Accepted Answer · 2021-10-31 14:39:53Z

I had a similar situation, where I needed a "changed record collection" holding the entire record when the current record was either new or had any changes when compared to the previous record. This was my code:

# get current and previous CSV
$current = Import-Csv -Path $current_file
$previous = Import-Csv -Path $previous_file

# collection with new or changed records
$deltaCollection = New-Object Collections.Generic.List[System.Object]

:forEachCurrent foreach ($row in $current) {

  $previousRecord = $previous.Where( { $_.Id -eq $row.Id } )
  $hasPreviousRecord = ($null -ne $previousRecord -and $previousRecord.Count -eq 1)
  if ($hasPreviousRecord -eq $false) {
    $deltaCollection.Add($current)
    continue forEachCurrent
  }

  # check if value of any property is changed when compared to the previous 
  :forEachCurrentProperty foreach ($property in $current.PSObject.Properties) {

    $columnName = $property.Name

    $currentValue = if ($null -eq $property.Value) { "" } else { $property.Value } 
    $previousValue = if ($hasPreviousRecord) { $previousRecord[0]."$columnName" } else { "" }

    if ($currentValue -ne $previousValue -or $hasPreviousRecord -eq $false) {
      $deltaCollection.Add($currentCenter)
      continue forEachCurrentProperty
    }

  }

}

Collectives™ on Stack Overflow

compare two csv using powershell and return matching and non-matching values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related