For some organisations, Migration Manager from Microsoft is a perfect fit for their migration requirements. And json is a good data format if you want to import multiple mappings at once. This post explains a simple way of using PowerShell to convert typical CSV-mappings to the Migration Manager json-format.

Setting the scene

Imagine a typical migration data mapping file, that is commonly partially populated (UNC paths) from an inventory scan (e.g. using TreeSize from Jam Software) and completed through collaboration with the business based on their mapping requirements. And columns are comma or semi-colon delimited.

So network drive UNC paths on the left, and target locations on the right!

FullPathteamNamemailNickNamechannelNamechannelType
\\server\Operations\CommonOperationsdep-operations-intGeneralStandard
\\server\Operations\MonitoringOperationsdep-operations-intMonitoringStandard
\\server\Operations\FinanceOperationsdep-operations-intFinancePrivate
\\server\Operations\TeamOperationsdep-operations-intTeamPrivate
\\server\Operations\PlanningOperationsdep-operations-intPlanningStandard

In this example multiple rows contain mappings to different Teams-channel folders in SharePoint Online. Some are standard, while others are Private (different site). This is often the case for the projects I’m involved in.

Based on the mapping file, the teams are also created. Sometimes using the API of a cloud-based provisioning solution applying templated settings for a particular usage scenario. Or simply using PowerShell cmdlets from the PnP.PowerShell or MicrosoftTeams modules.

Migration Manager mappings

While Migration Manager supports CSV and json-formats, the latter has some benefits.

The CSV-format requires you to leave some empty columns which I don’t like. The following example shows the CSV-format with the empty columns shown as consecutive commas.

FileSharePath,,,SharePointSite,DocLibrary,DocSubFolder
\\server\Operations\Common,,,https://contoso.sharepoint.com/sites/dep-operations-int,Documents,General

The json-format doesn’t have the empty columns issue and is much richer as it supports for mapping-specific settings. This includes options like whether or not to migratie hidden items or setting filters like date or which filetypes to skip.

You might notice the escape-characters for the ‘SourcePath’-element. Don’t worry about that. Migration Manager can handle this without any issues.

You can add up to 50.000 tasks for a single tenant, but typically you will probably split this up into clustered tasks per json-file for an organisational entity.

{
	"Tasks": [
		{
			"SourcePath": "\\\\server\\Operations\\Common",
			"TargetPath": "https://contoso.sharepoint.com/sites/dep-operations-int",
			"TargetList": "Documents",
			"TargetListRelativePath": "General",
			"Settings": {
				"MigrateHiddenItems": false,
				"MigrateItemsCreatedAfter": "2010-01-01",
				"MigrateItemsModifiedAfter": "2010-01-01",
				"SkipFilesWithExtensions": "pst:db",
			}
		}
	]
}

Converting to json

Converting the CSV to Migration Manager json-format is fairly easy to accomplish. The following basic script does exactly that, including support for private channels and an instance of the additional settings mentioned earlier.

#Set variables
[string]$inputFile = 'teamsMapping.csv'
[string]$outputFile = 'teamsMapping.json'
[string]$tenantName = 'contoso'
[string]$listName = 'Documents'
[string]$fromFilterDate = '2010-01-01' #must be a string format.

#Import from the input file
$table = Import-Csv -Path "$PSScriptRoot\$inputfile" -Delimiter ','

#Create an array to store the mappings
[array]$mappings = @()

#Perform all steps for each row
foreach ($row in $table) {

    #Set base url
    $baseUrl = "https://$tenantName.sharepoint.com/sites/$($row.mailNickName)"

    #Handle settings for channel types
    switch ($row.channelType) {

        #Standard channels
        'Standard' {

            #Set the Url and folder variables
            $url = $baseURL
            $channelFolder = $row.channelName

        }

        #Private channels
        'Private' {

            #Remove white space from the channelname
            $channelName = ($row.channelName -replace '[^a-zA-Z0-9]', '')

            #Set the site Url
            $url = "$baseURL-$channelName"

            #Set the foldername
            $channelFolder = $row.channelName

        }
    }

    #Add a new mapping including the foldername from the source path
    $mapping = [ordered]@{
        'SourcePath'             = $row.'FullPath'
        'TargetPath'             = $url
        'TargetList'             = $listName
        'TargetListRelativePath' = $channelFolder

    }

    #Add date scoping if applicable
    if ($fromFilterDate) {
        $mapping.Settings = @{
            'MigrateItemsCreatedAfter'  = $fromFilterDate
            'MigrateItemsModifiedAfter' = $fromFilterDate
        }
    }

    #Add mapping to mappings
    $mappings += $mapping

}

#Create new object to add the mappings as tasks
$data = @{}

#Add the "Tasks" node to the object and add the mappings
$data.Add('Tasks', $mappings)

#Export the mappings to json format
ConvertTo-Json $data -Depth 3 | Out-File "$PSScriptRoot\$outputFile" -Force

The result of running the scripts is a valid json-file, including the date filter settings per mapping. Please note that this is not required. You can also specify this in the Global Settings. Just be aware, that these settings are only inherited when the tasks are initially imported. If you change the Global settings after the import, the tasks will not inherit the changes.

{
	"Tasks": [
		{
			"SourcePath": "\\\\server\\Operations\\Common",
			"TargetPath": "https://contoso.sharepoint.com/sites/dep-operations-int",
			"TargetList": "Documents",
			"TargetListRelativePath": "General",
			"Settings": {
				"MigrateItemsCreatedAfter": "2010-01-01",
				"MigrateItemsModifiedAfter": "2010-01-01",
			}
		}
	]
}

This file can simply be imported into Migration Manager. I hope Microsoft will add API’s at some point, to import the mappings programmatically.

Migration Manage json inputfile