Microsoft Collaboration, CTO viewpoint, and New York

The blog for Office 365, Power Apps, Flow, SharePoint, cloud, ….. plus a little NYC

Getting Excel data into SharePoint.. The simple way

Written by Peter Ward on July 1, 2012 – 4:41 pm -

Scenario

You are working on a project that requires that migration of Excel data to be pushed into a SharePoint list, where the business process can leverage off all the benefits of SharePoint, such as workflows, views, alerts, etc.

Now the natural way to do this migration is to create a new list with the Import Spreadsheet option.  This approach is problematic for a number of reasons.

–          If there’s a lot of data with complex data types, not all the data maybe exported out of Excel

–          There is no error checking in the import process.

–          You can’t do any data manipulation in the process, ie.  Merging fields, or perhaps items from other lists

–          And the biggest drawback is that just when you’ve got the data perfect in the SharePoint list, the person who gave you the data in Excel, then emails you an updated data file.. Typical.

I recommend using MS Access for the following reasons:

-There is error handling

-Data can be manipulated, yes even different lists

-With 2010, this can even be used with InfoPath forms in lists

-You probably have Access already installed on your PC

Folks are often put off with using the MS Access program and view it as an alien body software.  It’s not and it’s easy to use, yes even for non technical users.

If you are doing a fair bit of importing of data from Excel, this approach this will save you a boat load of time, trying to get data correctly imported into SharePoint.

This video shows you how to do it.

Posted in BI and Productivity.