Friday, July 27, 2012

Importing and Exporting Data in Extranet Collaboration Manager (ExCM) 2010 – Part 2 – Importing

by Matthew McBride

In Part 1 of this series, I discussed how you can export data from the ExtranetCollaboration Manager for SharePoint 2010 (ExCM) database for use with other applications, projects, etc.  A far more common question we receive is whether or not companies with data for existing users can IMPORT that information into ExCM.  In the past, we have run across potential customers who have a large number of existing users stored in another application or database and would rather not have to go back and manually add or register them for access to their ExCM SharePoint extranet site.  By utilizing our free PowerShell utility, you can import users directly into an ExCM database.
As mentioned in the previous blog post for exporting data, after downloading the utility from link below, you'll need to edit the included Membership.config file utility to reflect the connection string and membership settings:

(Click on pictures for expanded view)
Next, we need to open up PowerShell (NOT the SharePoint Management Shell), and navigate to the extracted files:
The import file must be in a comma-delimited format.  For the purpose of this blog, I have created some sample data in an Excel spreadsheet and exported it as a text file:

Before we continue, let’s take a quick look at passwords and security questions.  As you can see from the above file, I have gone ahead and assigned both a password and a security answer for the users.  Optionally, you can generate a password using the SQL Membership Provider by using the “-passwordSource Generated” switch or use a separate password file with passwords and security questions with the “-passwordSource PasswordFile -passwordQuestionAndAnswerSource PasswordFile -passwordFile [filename]” switch.  Here is an example of what the password file would look like:

For this post, I’m just going to use the values in my original spreadsheet.  Here is what my command will look like, along with the results of the import:

If I go to my “Extranet Users” menu, I can see that all four accounts now appear there:

Additionally, you can create a process to send an email to each user containing the new login information such as username, password, password question and password answer:

import-csv .\Import.Results.csv | ? {$_.Status -eq "Success" } | % { Write-Host $_.Username $_.Email, $_.Password $_.PasswordQuestion $_.PasswordAnswer }
In summary, by utilizing our free Powershell utility, you can both import and export data to and from your ExCM database.  This can be useful for providing data to other applications or projects, or eliminating the need to manually create numerous users for your ExCM site.