Skip to main content

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

Updated this week

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, such as Site URLs, list names, List views, and more, to dictate the flow of your migration.

Note: As of the end of January 2026, Microsoft began deprecating the IDCRL cookie, meaning that only Browser and Modern authentication methods with the parameters -Browser and -ModernAuth will work in most Microsoft 365 tenants. To learn more about this change, see Important change to "Other user" authentication for Microsoft 365.

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 destination site is the only variable that changes with each pass of the foreach loop.

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 into the PowerShell application of your choice.

Import-Module Sharegate

# Define CSV file path
$csvFile = "C:\CSVfile.csv"

# Import CSV data into a table
$table = Import-Csv $csvFile -Delimiter ","

# Connect to the source site and get the source list
$srcSite = Connect-Site -Url "https://sourcetenant.sharepoint.com/sites/sitecollection" -Browser
$srcList = Get-List -Site $srcSite -Name "Site Pages"

# Define the destination connection
$dstSiteConnection = Connect-Site -Url "https://destinationtenantname-admin.sharepoint.com/" -Browser

# Loop through each row in the CSV
foreach ($row in $table) {
    # Clear previous variables for the destination site and list
    Clear-Variable -Name dstSite -ErrorAction SilentlyContinue
    Clear-Variable -Name dstList -ErrorAction SilentlyContinue
    
    # Connect to the destination site and get the destination list
    $dstSite = Connect-Site -Url $row.DestSite -UseCredentialsFrom $dstSiteConnection
    $dstList = Get-List -Name "Site Pages" -Site $dstSite
    
    # Copy content from source list to destination list
    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. Ensure your script uses the same delimiter as your file (a quick way to verify this is to open the CSV in Notepad).

  • Connect-site: this example uses -Browser to connect to Microsoft 365 instead of -ModernAuth because the latter has limitations with classic pages and classic web parts. To change the authentication method, see 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.

  • $dstSiteConnection: Change the URL to the address of your SharePoint admin center.

  • 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 in a folder, you have to specify the path relative to the library (folder/custompage.aspx).

  • -UseCredentialsFrom: This script uses Browser authentication with the -UseCredentialsFrom parameter to reuse a single connection. This script will prompt you for your Microsoft 365 credentials when you start it. To learn more or change your one, see Connect-Site and Avoid repeatedly entering your credentials with the browser and modern connection methods.

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

Modifying your script

As an example of how you can add variables to your script with the foreach loop statement, here is how you can copy a different page per site with that same script:

  1. Add a new SrcPage column in your CSV.

  2. Enter the name of the page you want to migrate from the source Site Pages library for each site in the list.

  3. Save your modified CSV.

  4. Modify your script by replacing "Home.aspx" with $row.SrcPage on the Copy-Content line.

    Copy-Content -SourceList $srcList -DestinationList $dstList -SourceFilePath $row.SrcPage

  5. Run the script, and all the sites will get the page you specified in the destination Site Pages library.

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.

    mceclip1.png


    ​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 into the PowerShell application of your choice.

Import-Module Sharegate

# Define CSV file path
$csvFile = "C:\CSVfile.csv"

# Import CSV data into a table
$table = Import-Csv $csvFile -Delimiter ","

# Source credentials
$srcUsername = "sourceusername"
$srcPassword = ConvertTo-SecureString 'sourcepassword' -AsPlainText -Force

# Destination connection
$dstSiteConnection = Connect-Site -Url "https://destinationtenantname-admin.sharepoint.com" -ModernAuth

# Set variables for source and destination sites
Set-Variable -Name srcSite, dstSite

# Loop through each row in the CSV
foreach ($row in $table) {
    # Clear previous variables for source and destination sites
    Clear-Variable -Name srcSite -ErrorAction SilentlyContinue
    Clear-Variable -Name dstSite -ErrorAction SilentlyContinue
    
    # Connect to the source and destination sites
    $srcSite = Connect-Site -Url $row.SourceSite -Username $srcUsername -Password $srcPassword
    $dstSite = Connect-Site -Url $row.DestSite -UseCredentialsFrom $dstSiteConnection
    
    # Copy site from source to destination
    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 to open the CSV in Notepad).

  • $srcUsername, $srcPassword: Replace "sourceusername" and 'sourcepassword' with your SharePoint credentials.

  • $dstSiteConnection: Replace the URL with the address of your destination tenant's SharePoint admin center.

  • 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.

  • -UseCredentialsFrom: This script uses Modern authentication with the -UseCredentialsFrom parameter to reuse a single connection. This script will prompt you for your Microsoft 365 credentials when you start it. If MFA isn't enforced on your tenant(s), you can set your script to use a username and password for unattended scheduling. Modern authentication has some copy limitations. To learn more, see Connect-Site and Avoid repeatedly entering your credentials with the browser and modern connection methods.

  • 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 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.

Did this answer your question?