Friday, 18 July 2014

How to Import Excel Spreadsheet to Lists in SharePoint 2010 / 2013

 Users asking you they have large data in Excel and want to import it into SharePoint; You might have dealt with it at least once if you are in SharePoint.

The primary option will be to import data onto SharePoint by creating a custom list from the Spreadsheet as said on this link - MS Link; there could  be situation where the list is already present and you want to update the data or not follow this approach and then you might think about the SharePoint Datasheet view

However this approach also have several limitations as the excel sheet values should need to be of the same datatype again SharePoint Datasheet view have some restrictions on columns with "Multiline columns" as  and so on... Now what is the best way, i was trying different things and nothing much was helping me out until I found the MS Access way  :)

Yes if you have MS Access service enabled on your farm you have a better and easy way to bulk upload data onto SharePoint in easy 4 steps, Here are the steps
[I have used MS Access 2010 and SharePoint 2010, Access Service enabled on SharePoint farm]

Step 1.
Open MS Access on your client machine > Select All files > MicrosoftSharePoint foundation ()





















Step 2.
Specify a SharePoint Site > Select  > Linked to the data Source by creating a linked table > Click Next



























Step 3

Select the Table which you want to import data > Click OK



























Step 4

Access will create a table which is linked on to SharePoint and you can easily upload, export or manage any data's and SharePoint list will be synced and updated immediately.



















Hope this helps.... Happy SharePointing....










1 comment:

Anonymous said...

Great solution for 80% of the problems, however, if the list contains a managed metadata column that column is NOT updateable, it is view only within MS ACCESS.