Importing and Exporting Data in Extranet Collaboration Manager for SharePoint 2010 (ExCM) – Part 1 – Exporting

by Matthew McBride

ExtranetCollaboration Manager for SharePoint 2010 (ExCM) is a SharePoint add-on that provides easy SharePoint extranet collaboration and simplified extranet administration. A common question we often receive about ExCM is if data from the ExCM database can be exported and/or data from another source can be imported into that database.  The answer is “yes.”  In Part 1 of this series, we will take a look at how we can export data from our ExCM database.

In a typical ExCM installation, the data is stored in a SQL database.  Suppose your Marketing Department wanted all of the e-mail addresses for the thousands of users currently using your Extranet site.  Obviously, if you were savvy enough with SQL you could write some script to export them.  But for those who are NOT familiar enough with SQL, we have developed a PowerShell script that you can use to both import and/or export data from that database used by ExCM.  Please note this is a FREE utility and that we do not provide support directly for the script.  While we definitely welcome feedback, we must give support priority to our other commercial products.

First, you'll need to download the utility and extract the contents somewhere on the Web Front End (WFE) server.  Here is direct link:

http://aws-software-s3.sharepointsolutions.com.s3.amazonaws.com/products/support/ExCM2010/Import-Export-ExtranetDirectory.zip

Once that’s done, you need to open up PowerShell (NOT the SharePoint Management Shell), and navigate to the extracted files (C:\Temp in this case). 

Next, you need to edit the Membership.config file provided along with the utility to reflect the connection string and membership settings.  These values can be found in the web.config file of the content site on which ExCM is currently installed.  The “data source” is the name of your SQL server (including the instance name if applicable):

A “readme” file is also provided which has both the instructions and some sample data for use with the utility.  For the purposes of this blog post, I am simply going to export the few current users from my demo environment.

So my expected output should only contain information for the four users above.  Now that I have made the necessary changes to the Membership.config file, I'm ready to run the script.  Below is the example from the readme file where “membershipProviderName” is the value you specified for your provider and “outputFile” is the name you want to assign to the exported data:

.\Export-ExtranetDirectory.ps1 -configurationFile Membership.config -membershipProviderName Ext -outputFile  Export.Results.txt

After copying and pasting the above command, I can follow the status within the PowerShell window:

As you can see, all four of my users were successfully exported.  A quick but important note about the Export process…the users’ current passwords and secret answers CANNOT be exported as this would be a major security issue.  When I open the export file, it looks like this:

You can now take this data and import into Excel as a comma-delimited file to create a cleaner and more robust file for others to view and manipulate:

You could even go as far as creating another PowerShell script yourself to iterate all of the users and send each one an email message, or using the output to perhaps import the data into another application such as CRM.
In Part 2 of this blog series, we will talk about importing existing data into the ExCM database.