Hi All,
Today, I want to share a handy PowerShell function I developed recently to enhance readability of ShareGate log files.
If you are using ShareGate for SharePoint migrations or management tasks, you might be familiar with the extensive Excel logs produced by ShareGate. While these logs are rich in details, they can sometimes be too rich in details. They often require some formatting for better readability or to highlight the necessary details. When doing a lot of migrations I found myself doing the same steps over and over again to these logs. Being lazy, I thought, “Someone should automate this!”. I sat down with my buddy PowerShell and this is what we came up with.
This function, Format-ShareGateLogFile, tackles this by opening a ShareGate log file in Excel format and applying a few changes. It does this by using Doug Finke’s excellent ImportExcel PowerShell Module. It adds a table to the first worksheet, formats the first column as "Date-Time", calculates the duration of the log file, and formats the duration as "[h]:mm:ss" in the last row. Finally, it saves the changes and closes the Excel file.
To use this function, you’ll have to download Logfiles.psm1 from my Github Repo. Then use Import-Module to import it into your PowerShell session.
After that’s done you can run Format-ShareGateLogFile. You will need to provide the path to the Excel file to format as an argument to the Path parameter, which is mandatory. For instance,
PS C:\> Format-ShareGateLogFile -Path "C:\path\to\ShareGateLogFile.xlsx"
This will format the Excel file located at "C:\path\to\ShareGateLogFile.xlsx" for readability.
The function also accepts two optional switches: Open and HideColumns. If you use the Open switch, the function will open the formatted Excel file automatically after it has finished formatting. The HideColumns switch will hide specified columns (E-U, W-AR, AT-BA) in the Excel file. For instance,
PS C:\> Format-ShareGateLogFile -Path "C:\path\to\ShareGateLogFile.xlsx" -HideColumns -Open
This will format the Excel file, hide the specified columns, and open the Excel file automatically after it has been formatted.
One neat feature of the function is that it can accept pipeline input for the `Path` parameter. This means you can pipe in a series of file paths to the function and it will format each file in turn. For instance,
PS C:\> Get-ChildItem -Path "C:\path\to\folder" -Filter "*.xlsx" | Format-ShareGateLogFile -HideColumns -Open
This will get all the Excel (.xlsx) files in the specified folder, and for each one, it will be formatted for readability, with specified columns hidden, and the Excel file opened automatically after it has been formatted.
This function has saved me heaps of time while working with ShareGate log files, and I hope it does the same for you. Happy scripting!
tk
ShortURL: https://www.toddklindt.com/PoshFormatSharegateLogs