SharePoint is a widely popular tool amongst developers for many reasons. Firstly, most of the core functionality, such as security, data management and more are already in place, meaning one less job for developers.
Secondly, SharePoint lists is a highly efficient system for managing records. In the most recent Microsoft update to the core design, lists can now handle a phenomenal 30 million records, therefore providing organizations with scalable record management systems for business applications. What makes this even more valuable, is that Microsoft increases this limit almost every year at Ignite.
So, building new applications utilizing SharePoint is evidently a great opportunity to enhance business processes, but what if we need to migrate historical data from a legacy system?
I came across a situation with a customer whilst on a project developing a custom application utilizing SharePoint Online. Once the application was ready to go live, my customer requested to move their historical data from the Microsoft Access database to the new system. After analyzing the current dataset from the CSV file provided by the customer, I discovered my customer was generating around 70,000 records a year and wanted to migrate three years’ worth of records.
Essentially, my objective here was to migrate the historical data (210,000 records) provided in a large CSV file to a new system designed in SharePoint Online.
If I were to achieve this objective by migrating the data manually it would be an extremely time-consuming, inefficient process. In order to make this process more automated and streamlined, the best tool to use is PowerShell.
In this blog, I will explain the steps I took to create a more simplified method for data migration and demonstrate how you can do this in your environment.
The Plan of Action in 4 Steps
- Review the exported CSV dataset to ensure that the SharePoint fields match the CSV data
- Download and install SharePoint Online Management Shell
- Install the PowerShell PnP library
- Write and run the script in PowerShell
Below is a graphic on how this process looks. Let’s get started!