Over the years I have been asked this question several times. “How can I export my Active Directory data or SharePoint data into a Visio-like org chart”. Look no further……We can leverage SharePoint and PowerShell to get at this data and export it in a format that is “Visio Friendly”.
SharePoint has a service application called the User Profile Service Application (UPSA). One of the primary features of the UPSA is to sync data from Active Directory. By default, when you setup a synchronization it pulls the Active Directory Manager field into a SharePoint user property also named Manager. We can use PowerShell to get that data and export it from SharePoint.
Note: PowerShell cmdlets are available with the Active Directory module that will allow you export directly from AD if needed.
Important: This script will only export user objects that have a manager defined. If there is no value in AD this script will not export the user.
Ok, let’s get started…
- Place the script on your SharePoint server.
- Update the following values within the script:
- $exportPath = “Enter the file export path” #This defines where our eventual CSV file will be located.
- $siteUrl = “Enter a valid site collection Url”
- Note: The log file will be placed in the directly where the script was run.
- Now, let’s run the script.
We should have an output file in our export path. Now we need to take this file and import into Visio.
- Open Visio and choose the Organizational Chart Wizard.
- Choose excel file
- Verify your mappings
- Pick your fields to display
- Pick the fields you want as data fields. I picked them all.
- Select your picture export folder and choose the PicId field to map to
- I choose to pick my pages manually but you can choose whichever one your want.
Visio will grind for a bit (especially if you map pictures). Once it is done, you have a pretty new org chart:)
*Keep in mind this snapshot IS from SharePoint but only sample data:)