Copy to and from multiple destinations with a foreach loop statement and a CSV file in PowerShell

You can use a foreach loop statement to copy to and from multiple locations listed in a CSV file.

This is what a foreach loop statement looks like:

foreach ($row in $table) { commands to be repeated for each row in your CSV }

The lines you add between the brackets of your foreach statement are repeated for each row in a CSV file. This allows you to establish a list of parameters like Site URLs, list names, List views, and more, to dictate the flow of your migration.

Index

• Examples
  • Copying a page to multiple sites using Copy-Content
  • Merging multiple Site Collections from a SharePoint farm to a Microsoft 365 tenant
• Things to consider

Examples

Copying a page to multiple sites using Copy-Content

This example uses the copy-content command to copy a home page from a source Site Pages library to many other Site Pages libraries. The only variable that will change for each pass of the foreach loop is the destination site.

Create a CSV guide for your migration

1. Create a new Excel document.
2. Name the first column DestSite.
3. List the URLs of all the sites where you want to copy the home page.
   
4. Save the file as a CSV.

Create your script

Copy and paste the following script in the PowerShell application of your choice.

Import-Module Sharegate
$csvFile = "C:\CSVfile.csv"
$table = Import-Csv $csvFile -Delimiter ","

$srcUsername = "sourceusername"
$srcPassword = ConvertTo-SecureString 'sourcepassword' -AsPlainText -Force
$dstUsername = "destinationusername"
$dstPassword = ConvertTo-SecureString 'destinationpassword' -AsPlainText -Force

$srcSite = Connect-Site -Url "http://farm/sites/sitecollection" -Username $srcUsername -Password $srcPassword
$srcList = Get-List -Site $srcSite -Name "Site Pages"
Set-Variable dstSite, dstList
foreach ($row in $table) {
    Clear-Variable dstSite
    Clear-Variable dstList
    $dstSite = Connect-Site -Url $row.DestSite -Username $dstUsername -Password $dstPassword
    $dstList = Get-List -Name "Site Pages" -Site $dstSite
    Copy-Content -SourceList $srcList -DestinationList $dstList -SourceFilePath "Home.aspx"
}

Adjust your script to make it work for you. Here are a few guidelines:

• $csvFile: Adjust the path so that it points to the CSV file you saved before.
• $table: The delimiter is the symbol your CSV uses to separate your column items. Make sure your script uses the same delimiter as your file (a quick way to verify this, is by opening the CSV with Notepad).
• $srcUsername, $srcPassword, $dstUsername, and $dstPassword: Replace "sourceusername", 'sourcepassword', "destinationusername", and 'destinationpassword' with your SharePoint or 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 $srcList: The source site and source list variables are outside of the foreach loop. This is because we will take the Home.aspx page from the same Site Pages library for each pass of the foreach loop.
• Set-variable and Clear-Variable: These commands help prevent an issue where a connection failure can cause your data to end up in the wrong destination.
• foreach: Loops the commands between the brackets for each row in your CSV file. In this case, it runs a copy of the Home.aspx file for Sitecollection1, then the same file migration for Sitecollection2, and so on.
• Copy-Content: -SourceFilePath is used to specify which file to copy. In this case, it is Home.aspx which should be at the root of the Site Pages library. If you want to copy another file located in a folder, you have to specify the path relative to the library (folder/custompage.aspx.)

Run your script once it's properly adjusted and tested.

Merging multiple Site Collections from a SharePoint farm to a Microsoft 365 tenant

This example uses the copy-site command to merge your site collections from a SharePoint farm into new site collections in Microsoft 365. The variables that will change for each pass of the foreach loop are the source and destination site URLs.

Create a CSV guide for your migration

1. Create a new Excel document.
2. Name the first column SourceSite.
3. Name the second column DestSite.
4. List the appropriate source and destination site collection URLs.
   
   Note: You can use the site collection report to get a list of your site collections.
5. Save the file as a CSV.

Create your script

Copy and paste the following script in the PowerShell application of your choice. 

$csvFile = "C:\CSVfile.csv"
$table = Import-Csv $csvFile -Delimiter ","

$srcUsername = "sourceusername"
$srcPassword = ConvertTo-SecureString 'sourcepassword' -AsPlainText -Force
$dstUsername = "destinationusername"
$dstPassword = ConvertTo-SecureString 'destinationpassword' -AsPlainText -Force

Set-Variable srcSite, dstSite
foreach ($row in $table) {
    Clear-Variable srcSite
    Clear-Variable dstSite
    $srcSite = Connect-Site -Url $row.SourceSite -Username $srcUsername -Password $srcPassword
    $dstSite = Connect-Site -Url $row.DestSite -Username $dstUsername -Password $dstPassword
    Copy-Site -Site $srcSite -DestinationSite $dstSite -Merge -Subsites
}

Adjust your script to make it work for you. Here are a few guidelines:

• $csvFile: Adjust the path so that it points to the CSV file you saved before.
• $table: The delimiter is the symbol your CSV uses to separate your column items. Make sure your script uses the same delimiter as your file (a quick way to verify this, is by opening the CSV with Notepad).
• $srcUsername, $srcPassword, $dstUsername, and $dstPassword: Replace "sourceusername", 'sourcepassword', "destinationusername", and 'destinationpassword' with your SharePoint or Microsoft 365 credentials.
• Connect-site: The command to connect to a SharePoint site. If you need to change the authentication method, see Connect Site.
• Set-variable and Clear-Variable: These commands help prevent an issue where a connection failure can cause your data to end up in the wrong destination.
• foreach: Loops the commands between the brackets for each row in your CSV file. In this case, it merges sitecollection1 from your source into sitecollection1 in your destination, then from sitecollection2 to sitecollection2, and so on.
• Copy-Site: Without the -Merge and -Subsites parameters, the script would copy the source site collection as a subsite of the destination site collection, and it would not include the source's own subsites.

Run your script once it's properly adjusted and tested.

Things to consider

Migration reports

Migration reports are automatically generated, and you will be able to find them in Tasks. You can also export the reports in your script with Export-Report.

Scheduling

You can schedule your migration to run it off-hours and optimize performance.

Incremental migrations

Copy & Replace is used by default with PowerShell. You will find how to adapt your script to perform an incremental migration in the Incremental update using PowerShell article.

Performance

If you have hundreds of gigabytes of data to migrate, we recommend creating multiple CSVs to run the migration in smaller batches.