ShareGate:Desktop provides excellent PowerShell support for performing fully scripted migrations from e.g. file shares to Microsoft Teams or SharePoint Online. Ensuring that the entire data set is migrated succesfully, reporting is key. ShareGate allows for detailed reports to be exported to Excel and CSV formats. The latter is useful in case further processing is required for reporting purposes. This post outlines some practical tips.
When scripting your migration using ShareGate:Desktop, it’s been possible for a while to export detailed (item/version level) reports to Excel format. This is useful for sharing individual reports to data stewards or data owners. The PowerShell syntax is simple:
Export-Report $result -Path $reportPath -DefaultColumns -NoItemVersions
The outcome of a Copy-Content or Import-Document cmdlet is used as input and the path determines the location AND file format of the report. In case the columns in the Task window for a migration job were customized, the -DefaultColumns parameter can be used to show, well, the default columns. The -NoItemVersions can be used in case you only want to share on a file level.
For further details, see https://support-desktop.sharegate.com/hc/en-us/articles/115000641008-Export-Report
With large-scale migrations, migration reports are part of the proof of having performed a complete and successful migration. Often this is combined with an item-count comparison of files and folders in source and destination. I’m planning to create a post on this shortly.
With large-scale migrations, the reports may need to be parsed for further processing for overall reporting purposes. Potentially the reports may even have to be imported into a database. The Excel format is less useful for this.
Fortunately, it is also possible to export the reports to CSV format (comma-separated) simply by specifying this for the file extension at the -Path parameter. The output can be processed by PowerShell using the Import-CSV cmdlet.
#Import the source file
$inputFile = Import-CSV $fileIn
#Gather data for specific content types
$files = $inputFile | Where-Object { $_.Type -eq 'File'}
$folders = $inputFile | Where-Object { $_.Type -eq 'Folder'}
#Gather data on migration outcome
$success = $inputFile | Where-Object { $_.Status -eq 'Success' -and $_.Type -ne 'Content'}
$warnings = $inputFile | Where-Object { $_.Status -eq 'Warning' -and $_.Type -ne 'Content'}
$errors = $inputFile | Where-Object { $_.Status -eq 'Error' -and $_.Type -ne 'Content'}
The examples above show you how you can query the report based on filters using column values. This will e.g. show you the exact amount of files and folders that were uploaded during a copy job, something which is not shown by default in the ShareGate:Desktop application.
One tip for these specific queries is to exclude the rows that have the value “Content” as the type as this is not a specific file or folder, but more like a summary object.
So this works perfectly, until you have errors in the report (a colleague brought this to my attention). When you open up the CSV-file including errors, you will notice that the error-messages include linebreaks (LF), which breaks the line patterns and will make it cumbersome to import the reports into a database.
Error while updating field 'Modified By (Editor)'=======================================
Version 13.0.0 (Assembly 13.0.0.0, Build 2371)
=========================
[0] DA-083-110 (RetryFailed: False) --- Sharegate.Common.Exceptions.SGFieldUpdateException: Property 'Modified By': The following users and groups were not found: Contoso Office 365 account The current user has been assigned.
--------------------
On the other hand, the example PowerShell based queries will still work.
But if you are having troubles importing the reports into a database because of this, there is a small remedy available.
#Import the source file
$inputFile = Import-CSV $fileIn
#Get the headers from the first record
$headers = $inputFile[0].PSObject.Properties | ForEach-Object { $_.Name }
#Iterate through all lines in the dataset to remove undesired linebreaks in column values
For ($r=0; $r -lt $inputFile.Count; $r++) {
For ($c=0; $c -lt $headers.Count; $c++) {
$inputFile[$r].($headers[$c]) = $inputFile[$r].($headers[$c]).Replace("`n","<br/>")
}
}
#Export the fixed file
$inputFile | Export-Csv -Path $fileOut -NoTypeInformation
The trick here is to look for the columns that include the linebreaks (`n) and replace it with an alternate text (e.g. “<br/>”) or character.
After fixing the issues for each errorline, the data queries can be done directly after or the fixed file can be exported to CSV format again.
I did report the issue to ShareGate with the suggestion to maybe include a switch in the Export-Report cmdlet to exclude the linebreaks. If implemented or having received other suggestions for the issue, I will update this post.
