Home›User provisioning›Generate an XML file from an Excel spreadsheet

Generate an XML file from an Excel spreadsheet

Use a sample PowerShell script to convert an Excel spreadsheet of user details into a valid profile-source XML file and post it to Interact.

Note: This script is an example of how to generate XML from different sources for use with Interact's profile sources. Customisation of this script is not supported by Interact. Use it as a starting point and tweak it as necessary.

Before you start

  • You need a General Profile Source configured in Interact. See General Profile Sources.
  • You need PowerShell and Microsoft Excel installed (the script uses the Excel COM object).

Overview

With PowerShell, you can convert an Excel spreadsheet of user details into an XML file that Interact can process for user synchronisation.

The script sets some variables, defines a set of helper functions that write each part of the XML, then runs a short execution sequence that reads the spreadsheet, builds the file and posts it to Interact. The walkthrough below explains the parts you are most likely to change.

The script

# Excel details
$excelPath = "{{excel_workbook_path}}"
$sheetName = "{{sheet_name}}"

$usernameCell   = "{{username_column}}" # E.g. 1 or "A" depending on the sheet's reference style
$firstNameCell  = "{{first_name_column}}"
$surnameCell    = "{{surname_column}}"
$emailCell      = "{{email_column}}"
$managerCell    = "{{manager_column}}"
$jobTitleCell   = "{{job_title_column}}"
$departmentCell = "{{department_column}}"
$locationCell   = "{{location_column}}"
$companyCell    = "{{company_column}}"

# Hash table for additional fields, key is the field name, value is the number of the cell holding the data
$additionalFields = @{"first_aider" = 13; "employee_id" = 14}

# Interact details
$uri             = "{{profile_sources_api_url}}" # E.g. "https://{{intranet_url}}/api/umi/{{source_id}}/upload"
$authtoken       = "{{authentication_token}}" # E.g. "12345" or "78fSfjsQhmBM" or any other value
$domain          = "{{profile_source_name}}"
$ldapId          = "{{source_id}}"
$xmlPath         = "{{xml_file_path}}"
$groupName       = "{{group_name}}" # E.g. "Intranet Users"
$newUserPassword = "{{new_user_password}}" # Can be excluded, but only if the sync option and password elements are adjusted accordingly

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

function Write-SyncOption([System.Xml.XmlTextWriter] $writer, [string] $optionName, [string] $optionValue){
    # Write a syncoption element using the passed values
    $writer.WriteStartElement('option')
    $writer.WriteAttributeString('name', $optionName)
    $writer.WriteString($optionValue)
    $writer.WriteEndElement()
}

function Write-DocumentBase([System.Xml.XmlTextWriter] $writer){
    # Write the document root and syncoptions elements
    $writer.WriteStartElement('syncdata')
    $writer.WriteAttributeString('version', '1')

    $writer.WriteStartElement('syncoptions')
    $writer.WriteAttributeString('domain', $domain)
    $writer.WriteAttributeString('ldapid', $ldapId)

    Write-SyncOption $writer 'syncCompanies' 'true'
    Write-SyncOption $writer 'syncLocations' 'true'
    Write-SyncOption $writer 'syncDepartments' 'true'
    Write-SyncOption $writer 'syncManagers' 'true'
    Write-SyncOption $writer 'actionDisabledUsers' 'd'
    Write-SyncOption $writer 'actionMissingDeletedUsers' 'd'
    Write-SyncOption $writer 'loginType' '1'
    Write-SyncOption $writer 'defaultCulture' '1'
    Write-SyncOption $writer 'newUserPasswordBehaviour' 'strict'

    $writer.WriteEndElement()
}

function Write-AdditionalField([System.Xml.XmlTextWriter] $writer, [string] $fieldName, [string] $fieldValue){
    # Write an additional field element using the passed values
    $writer.WriteStartElement('field')
    $writer.WriteAttributeString('name', $fieldName)
    $writer.WriteString($fieldValue)
    $writer.WriteEndElement()
}

function Write-ManagerElement([System.Xml.XmlTextWriter] $writer, [string] $manager){
    # Write the manager element for a user
    $writer.WriteStartElement('manager')
    if (![string]::IsNullOrWhiteSpace($manager) -and ($manager.ToLower() -ne 'undefined')) {
        $writer.WriteAttributeString('uid', $manager)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $manager)
        $writer.WriteAttributeString('email', $manager)
    } else {
        $writer.WriteAttributeString('uid', '')
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', '')
        $writer.WriteAttributeString('email', '')
    }
    $writer.WriteEndElement()
}

function Write-PrimaryOrganisationElement([System.Xml.XmlTextWriter] $writer, [string] $organisationType, [string] $organisationValue){
    # Write an organisation element using the passed values
    $writer.WriteStartElement('organisation')
    $writer.WriteAttributeString('type', $organisationType)
    $writer.WriteAttributeString('primary', 'true')
    $writer.WriteString($organisationValue)
    $writer.WriteEndElement()
}

function Write-OrganisationsElement([System.Xml.XmlTextWriter] $writer, [string] $department, [string] $company, [string] $location){
    # Write the organisations element for a user, including only fields that have a value
    $writer.WriteStartElement('organisations')

    if (![string]::IsNullOrWhiteSpace($department)) {
        Write-PrimaryOrganisationElement $writer 'department' $department
    }

    if (![string]::IsNullOrWhiteSpace($company)) {
        Write-PrimaryOrganisationElement $writer 'company' $company
    }

    if (![string]::IsNullOrWhiteSpace($location)) {
        Write-PrimaryOrganisationElement $writer 'location' $location
    }

    $writer.WriteEndElement()
}

function Write-UsersElement([System.Xml.XmlTextWriter] $writer, [System.Object] $sh){
    # Write the users element: read each spreadsheet row (row 1 is the header) and create a user element
    $rows = ($sh.UsedRange.Rows).count

    $writer.WriteStartElement('users')
    $writer.WriteAttributeString('TotalUsers', $rows - 1)
    for($row = 2 ; $row -le $rows ; $row++)
    {
        $username = $sh.Cells.Item($row,$usernameCell).value2
        $firstname = $sh.Cells.Item($row,$firstNameCell).value2
        $surname = $sh.Cells.Item($row,$surnameCell).value2
        $email = $sh.Cells.Item($row,$emailCell).value2
        $manager = $sh.Cells.Item($row,$managerCell).value2
        $jobTitle = $sh.Cells.Item($row,$jobTitleCell).value2
        $department = $sh.Cells.Item($row,$departmentCell).value2
        $location = $sh.Cells.Item($row,$locationCell).value2
        $company = $sh.Cells.Item($row,$companyCell).value2

        $writer.WriteStartElement('user')
        $writer.WriteAttributeString('uid', $username)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $username)
        $writer.WriteAttributeString('email', $email)

        $writer.WriteStartElement('person')
        $writer.WriteElementString('firstname', $firstname)
        $writer.WriteElementString('surname', $surname)
        $writer.WriteElementString('title', '')
        $writer.WriteElementString('initials', '')
        $writer.WriteElementString('jobtitle', $jobTitle)
        $writer.WriteElementString('phone', '')
        $writer.WriteElementString('mobile', '')
        $writer.WriteElementString('fax', '')
        $writer.WriteElementString('extension', '')
        $writer.WriteElementString('address', '')
        $writer.WriteEndElement()

        $writer.WriteElementString('statusenabled', 'true')
        $writer.WriteElementString('password', $newUserPassword)
        $writer.WriteElementString('culture', '1')
        $writer.WriteStartElement('language')
        $writer.WriteAttributeString('id', '-1')
        $writer.WriteEndElement()

        # Additional fields: one element per entry in the $additionalFields hash table
        $writer.WriteStartElement('additionalfields')
        foreach ($af in $additionalFields.GetEnumerator()) {
            $afValue = $sh.Cells.Item($row,$af.Value).value2
            Write-AdditionalField $writer $af.Name $afValue
        }
        $writer.WriteEndElement()

        Write-ManagerElement $writer $manager
        Write-OrganisationsElement $writer $department $company $location

        $writer.WriteEndElement()
    }
    $writer.WriteEndElement()
}

function Write-GroupsElement([System.Xml.XmlTextWriter] $writer, [System.Object] $sh){
    # Write the groups element: place every spreadsheet user into a single group
    $rows = ($sh.UsedRange.Rows).count
    $memberCount = $rows - 1

    $writer.WriteStartElement('groups')
    $writer.WriteAttributeString('TotalUsers', $memberCount)
    $writer.WriteAttributeString('TotalGroups', 1)

    $writer.WriteStartElement('group')
    $writer.WriteAttributeString('uid', 'group1')
    $writer.WriteAttributeString('dn', '')
    $writer.WriteAttributeString('name', $groupName)
    $writer.WriteAttributeString('UserCount', $memberCount)

    $writer.WriteStartElement('users')
    for($row = 2 ; $row -le $rows ; $row++)
    {
        $username = $sh.Cells.Item($row,$usernameCell).value2
        $email = $sh.Cells.Item($row,$emailCell).value2

        $writer.WriteStartElement('user')
        $writer.WriteAttributeString('uid', $username)
        $writer.WriteAttributeString('dn', '')
        $writer.WriteAttributeString('username', $username)
        $writer.WriteAttributeString('email', $email)
        $writer.WriteEndElement()
    }
    $writer.WriteEndElement()

    $writer.WriteEndElement()
}

###########################################
#
# The main execution sequence of the script
#

# Set up the XML document
$xmlWriter = New-Object System.Xml.XmlTextWriter($xmlPath, $null)
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$xmlWriter.IndentChar = "`t"
$xmlWriter.WriteStartDocument()

# Build the syncoptions
Write-DocumentBase $xmlWriter

# Open the Excel workbook and worksheet
$excel = New-Object -ComObject Excel.Application
$wb = $excel.Workbooks.Open($excelPath)
$sh = $wb.Sheets.Item($sheetName)

Write-Host "Processing users..."
Write-UsersElement $xmlWriter $sh

Write-Host "Processing groups..."
Write-GroupsElement $xmlWriter $sh

# Close Excel
$wb.Close($false)
$excel.Quit()

# Close the document and flush to disk
$xmlWriter.WriteEndElement()
$xmlWriter.Flush()
$xmlWriter.Close()

Write-Host "Complete"

# Deliver the file to the API endpoint
Invoke-RestMethod -Uri $uri -Method Post -InFile $xmlPath -ContentType "multipart/form-data" -Headers @{'X-ApiKey'=$authtoken}

Step 1: Set up the variables

At the top of the script, set the variables that relate to your spreadsheet and your Interact instance:

# Excel details
$excelPath = "{{excel_workbook_path}}"
$sheetName = "{{sheet_name}}"

$usernameCell   = "{{username_column}}" # E.g. 1 or "A" depending on the sheet's reference style
$firstNameCell  = "{{first_name_column}}"
$surnameCell    = "{{surname_column}}"
$emailCell      = "{{email_column}}"
$managerCell    = "{{manager_column}}"
$jobTitleCell   = "{{job_title_column}}"
$departmentCell = "{{department_column}}"
$locationCell   = "{{location_column}}"
$companyCell    = "{{company_column}}"

# Hash table for additional fields, key is the field name, value is the number of the cell holding the data
$additionalFields = @{"first_aider" = 13; "employee_id" = 14}

# Interact details
$uri             = "{{profile_sources_api_url}}" # E.g. "https://{{intranet_url}}/api/umi/{{source_id}}/upload"
$authtoken       = "{{authentication_token}}" # E.g. "12345" or "78fSfjsQhmBM" or any other value
$domain          = "{{profile_source_name}}"
$ldapId          = "{{source_id}}"
$xmlPath         = "{{xml_file_path}}"
$groupName       = "{{group_name}}" # E.g. "Intranet Users"
$newUserPassword = "{{new_user_password}}" # Can be excluded, but only if the sync option and password elements are adjusted accordingly

Each ...Cell variable is the column that holds that piece of data in your spreadsheet. The $additionalFields hash table maps Interact additional field names to the spreadsheet columns that hold their values. The script assumes the first row of the sheet is a header, so it reads data from row 2 onwards.

The Write- functions that follow generate the XML for each user, the single group and its members, and the synchronisation options. You should not need to change them unless you want to map different spreadsheet columns.

Step 2: Understand the synchronisation options

The synchronisation options are set in the Write-DocumentBase function. When you use profile sources, Interact takes these options from the XML source itself rather than from the screens within Interact. Each option is described below.

  • syncCompanies — whether to synchronise user companies. If a company does not exist in Interact, it is created automatically and the user is assigned to it.
  • syncLocations — whether to synchronise user locations. If a location does not exist in Interact, it is created automatically and the user is assigned to it.
  • syncDepartments — whether to synchronise user departments. If a department does not exist in Interact, it is created automatically and the user is assigned to it.
  • syncManagers — whether to synchronise the manager relationship between users.
  • actionDisabledUsers — what to do with users marked as disabled in the XML:
    • x — do nothing; do not update the Interact status of those users.
    • d — deactivate those users in Interact.
    • a — deactivate and archive users in Interact.
  • actionMissingDeletedUsers — what to do with users previously created in Interact with this profile source, or not assigned to any groups in the XML. The options are the same as above.
  • loginType — must be present and set to the appropriate authentication method. See the schema documentation.
  • defaultCulture — must be present and set to the appropriate culture. See the schema documentation.
  • newUserPasswordBehaviour — how Interact creates passwords for new users:
    • strict — use the password specified in the user part of the XML.
    • random — create a random password for each user.

Step 3: Understand the main execution sequence

The main part of the script (under The main execution sequence of the script) ties everything together. It sets up the XML writer, writes the synchronisation options with Write-DocumentBase, opens the Excel workbook and worksheet with the Excel COM object, then calls Write-UsersElement and Write-GroupsElement to write the users and the group. Finally it closes Excel, closes and flushes the file, and posts it to your profile source endpoint with Invoke-RestMethod.

Troubleshooting

PowerShell reports errors from the script itself, and Interact returns useful error messages if the XML is invalid, so troubleshooting is straightforward. See Troubleshooting for more information.

Section: User provisioning