Using PS to compare a list of User in two Excel workbook of different months.

Welcome Forums General PowerShell Q&A Using PS to compare a list of User in two Excel workbook of different months.

Viewing 7 reply threads
  • Author
    Posts
    • #28243
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      Hi All,

      I need help in creating a script to compare two different excel sheets with almost same data.

      We have a report in which all members of an application are saved with their IDs, Last name, First name, Email ID, Roles and etc.. fields.

      every month several users gets added to it and also removed from this list. So we have to compare them with 2 conditions. Checking all users from OldFile with their User ID in Column A – if

      Condition One – Check OldFileUsers with NewFile and if OldFileUser is not in the NewFile then Show them as Terminated Users.
      Condition Two – Check NewFileUser with OldFile and if NewFileUser is not in the OldFile then Show them as New User.

      with the help of PS we need to give a path and it will pick the latest 2 files and compare them automatically.

      Can you please help me to find the solution, i am in the very beginning stage of PS and don’t have scripting background.

      Thanks all in advance.

      Best Regards,
      Vicky

    • #28245
      Participant
      Topics: 16
      Replies: 108
      Points: 0
      Rank: Member

      Use somthing like this:
      Compare-Object -ReferenceObject $(gc C:\Test\PowershellScriptTest\test1.csv) -DifferenceObject $(gc C:\Test\PowershellScriptTest\test.csv)

    • #28247
      Participant
      Topics: 6
      Replies: 236
      Points: 23
      Rank: Member
      # sample file uses headings of ID,Last,First,Email,Role
      Push-Location -Path C:\Ephemeral # my testing directory
      $old = Import-Csv -Path oldfile.csv
      $new = Import-Csv -Path newfile.csv
      $results = Compare-Object -ReferenceObject $old -DifferenceObject $new -Property ID, Last, First | foreach {
          if ($_.SideIndicator -eq '=>') 
          {
              $status = "New"
          }
          else
          {
              $status = "Terminated"
          }
          [PSCustomObject]@{
              ID = $_.ID
              Status = $status
              Name = "$($_.first) $($_.last)"
          }
      }
      # Sample outputs - pick one or more, your choice
      $results
      $results | Format-Table -AutoSize
      $results | Out-GridView
      $results | Export-Csv -Path foo.csv -NoTypeInformation -Encoding ASCII
      $results | Out-File -FilePath foo.txt -Encoding ASCII
      $results | Export-Clixml -Path foo.xml -Encoding ASCII
      
    • #28386
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      Thanks Bob, I tried to run this script and its working too. but in .csv file its not giving proper values/output. what do i need to do to view all fields(ID,Last,First,Email,Role etc…) in the output.csv file?

      The output coming in csv file is :

      IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count
      FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2

    • #28387
      Participant
      Topics: 6
      Replies: 236
      Points: 23
      Rank: Member

      You would have to add the additional properties to the Compare-Object line, and then also add those properties to the new [PSCustomObject] hash.

      If you copied the script correctly as above, you should be seeing output like this.

      "ID","Status","Name"
      "feverj","New","Johnny Fever"
      "davisg","New","Gena Davis"
      "rosy","New","Teddy Roosevelt"
      "foobar","Terminated","Foo Bar"
      "dowi","Terminated","Ivy Dow"
      

      What version of PowerShell are you running?

    • #28419
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      We are running PS 1.0 on windows 7.

    • #28420
      Participant
      Topics: 16
      Replies: 108
      Points: 0
      Rank: Member

      Windows 7 comes with PS 2.0 not PS 1.0. You can upgrade to PS 4.0 from the Microsoft site. http://www.microsoft.com/en-us/download/details.aspx?id=40855

    • #28463
      Participant
      Topics: 1
      Replies: 3
      Points: 0
      Rank: Member

      Thanks Bob & Wilfredo for your kind help. I am able to get the results.

      Thanks a lot!

Viewing 7 reply threads
  • The topic ‘Using PS to compare a list of User in two Excel workbook of different months.’ is closed to new replies.