Import Metadata with an Excel spreadsheet in PowerShell

You can add custom metadata to your list items and documents by using an Excel spreadsheet in PowerShell. You can use an Excel spreadsheet with the following commands:

• Copy-Content
• Import-Document
• Import-GoogleDriveDocument
• Import-BoxDocument

This method replicates importing the file in the Export metadata and import and copy process.

Index

• Create your Excel spreadsheet
• Create your script

Create your Excel spreadsheet

To create the file, follow steps (1) to (12) from the Export metadata and import and copy article.

We suggest reviewing the Editing the file section of the Export metadata and import and copy article before applying your modifications at step (11).

Note: You cannot export the Excel spreadsheet with PowerShell. It must be done from the application.

Create your script

Use -ExcelFilePath on your copy line to run your migration with your Excel metadata spreadsheet.

Here is an example using Copy-Content:

Import-Module Sharegate
$srcUsername = "sourceusername"
$srcPassword = ConvertTo-SecureString "sourcepassword" -AsPlainText -Force
$dstUsername = "destinationusername"
$dstPassword = ConvertTo-SecureString "destinationpassword" -AsPlainText -Force
$srcSite = Connect-Site -Url "http://myfarm1/sites/mysourcesite" -Username $srcUsername -Password $srcPassword
$dstSite = Connect-Site -Url "http://myfarm1/sites/mydestinationsite" -Username $dstUsername -Password $dstPassword
$srcList = Get-List -Name "mysourcelist" -Site $srcSite
$dstList = Get-List -Name "mydestinationlist" -Site $dstSite
Copy-Content -SourceList $srcList -DestinationList $dstList -ExcelFilePath "c:\Users\myUser\Desktop\myExcelFile.xslx"

Here are some guidelines for the script:

• $srcUsername, $srcPassword, $dstUsername, and $dstPassword: Replace "sourceusername", "sourcepassword", "destinationusername", and "destinationpassword" by your matching Microsoft 365 credentials.
• Connect-Site: If you need to change the authentication method, you will find how to do it in the Connect Site article.
• $srcSite, and $dstSite: Adjust the URLs to match your source site and destination site.
• $srcList, and $dstList: Replace "mysourcelist" and "mydestinationlist" by the names of the source list, and destination list.
• -ExcelFilePath: Adjust the path to match the location of your Excel metadata file. This parameter can be used with Copy-Content, Import-Document, Import-GoogleDriveDocument, and Import-BoxDocument as well.