Easy Data Migration in SharePoint: How we retrieved, cleaned and migrated 24 SharePoint list and libraries with 25,000 items using Excel in a couple hours while watching football​

Here at SP Marketplace, we recently upgraded our CRM and order system on SharePoint to a new version of the application. Installing and configuring the new CRM was the easy part, migrating the data and documents was the hard part.

The CRM is central to our business and to ensure that all data was up to date we had to perform the change over a weekend. Truly an almost impossible challenge given that the system has 24 interrelated lists and libraries with more than 9000 items in some. Also, some of the choices available for fields in the new system differed from the old, which required us to modify items from old status to new status fields. On top of that, we had to clean up duplicate items.

Because the new system had existing lists, we could not just use the Excel import function in SharePoint. We needed to append data and maintain lookups, choice, and user fields. We could have used some excellent tools like Sharegate or MetaVis, but they were overkill for this project because we were not moving sites or whole lists. Plus, these tools were too expensive for our needs and budget.

So how did we resolve this situation? Enter SP DataManager, an Excel add-in that transforms Microsoft Excel into a SharePoint data and document management powerhouse.

In a couple hours, while watching college football no less, we migrated and cleaned 20 lists that contained more than 25,000 items, and four document libraries with over 4000 documents.

From Excel, we connected SP DataManager to the old site and the new site, defined source lists and destination lists, and mapped fields between the two. Below is the process we used: 

1. Prioritized the lists by lookup relationships – parent / child
2. Retrieved each list or library into Excel, cleaned it (duplicates, modify status or choice fields) and migrated it to the new list or lists (lists were not one-to-one between the systems)
3. Archived old items to reduce list size 
4. Reviewed the migrated data and performed some tests to verify the new CRM was working correctly
5. Announced the availability of the new CRM Sunday night

Note: This was not a simple migration. Our old system had one list for prospects that included all presale prospects from lead to opportunity. The new system had a separate leads list and opportunities list. So we had to use views to filter the data to split the old prospects list into the two new lists.

With SP DataManager we were able to complete the migration in a shorter amount of time than we expected, and it literally blew our expectations away. It was way faster than we thought it would be; as an example, it retrieved several thousand items in less than a minute. Setting it up and using it required little technical knowledge. And, we were able to do all of this as business power users.

Later in the week the sales team decided they wanted to change a prospect drop-down to something else. We used SP DataManager to download all affected items into Excel, more than 1000; conduct a find and replace; then updated all the records back into the SharePoint list in less than 10 minutes.

As the CEO of SP Marketplace, before launching SP DataManager I insisted we do what we do with any product launch and use it in production before making it available to our customers. And to take it one step further, I made sure that it can be used by any power user.  

While I am hesitant to toot our own horn for a customer success story, I will say that in this case I was blown away by the potential of this tool and had to share it with others. Even as a vendor we face the same challenges with our internal systems as customers.
​
SP Marketplace provides out-of-the-box portals and business applications on Office 365 and SharePoint. One of the biggest challenges is moving data from old systems to the new SharePoint applications we provide. 
While our customers have praised our solutions, they complain about the pain of moving documents and data into SharePoint. So we worked with our close partner DC Solutions to come up with a simple tool to address this issue. What they came up with is SP DataManager and it was instrumental, to say the least, in helping SP Marketplace migrate data. 

If you need to migrate data or documents to SharePoint, move SharePoint data, archive data to get below the 5000 item limit, or just need to clean your SharePoint data, why not try SP DataManager? And, with a 30-day limited trial period, you can’t go wrong. Learn more about SP DataManager online and to download it today.

by Darrell Trimble via Everyone's Blog Posts - SharePoint Community