none
PowerShell: Search CSV Data and Compare to SQL Database RRS feed

  • Question

  • Hey Scripters!

    We're in the process of migrating from Lotus Notes to Exchange 2010.  A huge part of this migration is moving all of the current BlackBerry user's from Lotus Notes to Exchange.  On a good day, we have ~1000 active BlackBerry users and because we can't simply use the same BlackBerry Lotus Notes environment with Exchange 2010, we're left with creating several new BlackBerry user accounts in the new BlackBerry Exchange environment.  I'd like to automate this arduous process with a simple PowerShell script.

    The idea being:

    1) Once the user has been migrated from Lotus Notes to Exchange 2010, their email address will be added to a CSV file.
    2) The script should then search this CSV file and compare it to the current SQL database for existing BlackBerry users.  It should then add only those existing users (with server name) to a text file.
    3) The script will then execute the BlackBerry Server Admin client and create new user accounts based on the text file in #2.

    This brings me to my first question...using PowerShell, how do I compare data between the CSV file and the SQL database to determine the active users?  Next, how do I parse this data into a simple text file?

    Monday, September 24, 2012 5:33 PM

Answers

  • Thanks for the fast reply.  I'll move forward in exporting the data to CSV and then run a comparison.  Can I then export/convert the matched data into a text file?

    Why not?  Just use the comparison to retrive the records that match and output to a text file in any format you want.

    compare-object -ReferenceObject $csv1 -DifferenceObject $csv2  -IncludeEqual|%{$_.InputObject}


    ¯\_(ツ)_/¯

    • Marked as answer by DrivelFree Tuesday, September 25, 2012 9:29 AM
    Monday, September 24, 2012 6:59 PM

All replies

  • This brings me to my first question...using PowerShell, how do I compare data between the CSV file and the SQL database to determine the active users?  Next, how do I parse this data into a simple text file?

    You don't. You write a script to export the data to a CSV and do the compare or write a script to query the database one record at a time.  'ExecuteQuery" can be used to easily test for the existence of a match.

    Look in the repository for examples of how to use ADO.NET to access SQLServer from PowerShell.  You can install the SQLServer tools and use the PowerShell console for SQLServer SQLPS which will give you the database connections directly.  You can then use basic PosH methods to read the sql tables.

    I would opt for exporting the data from SQLServer as a comma delimited file with the fields you want and using Compare-Object to compare the two CSV files.

    Another missed method is to use The Active Directory provider for SQL Server to attach to AD and use SQL to join AD to the SQL table on the email address.  Tis assumes the the email address is the 'mail' address and not the 'proxyAddresses' array.


    ¯\_(ツ)_/¯

    Monday, September 24, 2012 5:44 PM
  • Thanks for the fast reply.  I'll move forward in exporting the data to CSV and then run a comparison.  Can I then export/convert the matched data into a text file?
    • Marked as answer by DrivelFree Tuesday, September 25, 2012 9:29 AM
    • Unmarked as answer by DrivelFree Tuesday, September 25, 2012 9:29 AM
    Monday, September 24, 2012 5:52 PM
  • Thanks for the fast reply.  I'll move forward in exporting the data to CSV and then run a comparison.  Can I then export/convert the matched data into a text file?

    Why not?  Just use the comparison to retrive the records that match and output to a text file in any format you want.

    compare-object -ReferenceObject $csv1 -DifferenceObject $csv2  -IncludeEqual|%{$_.InputObject}


    ¯\_(ツ)_/¯

    • Marked as answer by DrivelFree Tuesday, September 25, 2012 9:29 AM
    Monday, September 24, 2012 6:59 PM
  • Thanks!
    Tuesday, September 25, 2012 9:29 AM