get List items from SharePoint Online to SharePoint on-premise

Veeru Dasa 21 Reputation points


I am having SharePoint online Site with a list containing List Fields, basically want to keep my data in Online.

I need to get all the list items into my SharePoint 2013 On premises farm Site and display on the publishing page or site pages.

help me to solve this issue.


A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,893 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,726 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Allen Xu_MSFT 13,781 Reputation points

    Hi @Veeru Dasa ,

    To get all the list items from SharePoint Online and display them on SharePoint On-premise pages, please take a reference to the below steps.

    1.Access the source list in SharePoint Online and Select Export > Export to CSV file in the ribbon.
    2.Use the below PowerShell script to import from CSV file to a list in SharePoint-Onpremise.

     Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue  
    #Read the CSV file  
    $CSVData = Import-CSV -path "C:\Data.csv"  
    #Get the Web  
    $web = Get-SPWeb -identity "http://sp/"  
    #Get the Target List  
    $list = $web.Lists["test01"]  
    #Iterate through each Row in the CSV  
    foreach ($row in $CSVData)  
       $item = $list.Items.Add();  
       $item["Title"] = $row.Title  
       $item["Test"] = $row.Test  
       $item["Description"] = $row.Description  

    Note: In my case, my source list in the .csv file is shown in the below screenshot and the .csv file is stored in C:\Data.csv. My target list in SharePoint On-premise is named "test01". You have to create the target list and create the same columns(names and column types) as the source list in the target list before executing the script.
    You need to modify above script based on the values of your own list/columns/site. If you have People fileds or Date fileds, you have use svript like below in foreach ($row in $CSVData) to import those fields to the list.

     #Set the People Picker Field value  
     $item["People"] = Get-SPUser -Identity $row.People -web "http://sp/"  
     #Set the Date Field value  
     $item["Date"] = Get-Date $row.Date  

    3.After importing from .CSV file to the SharePoint On-premise list, You can access the page > edit the page > INSERT Web Part > Select the list from the Apps > Add.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.