SharePoint is used everywhere especially for document and content management. Nowadays, data that were stored in excel are being moved to SharePoint , thus making the ease of access and management of data. To facilitate this, SharePoint has an app(list) called “Import Spreadsheet” as shown below:
This approach is known to everyone where we select Import Spreadsheet list, provide a name and description, browse the excel file, select the range of cells to be imported. But only few of us know how to connect from excel to SharePoint. This article will cover the later approach. Let us see how to do.
- Open desired sheet in excel and select the range of cells to be moved to SharePoint(both rows and columns)
- Click on Insert->Table. In create table dialog box, check the range and hit ok
- Now, you will be able to see Design Tab in the ribbon.
- Under External Table Data section, click on Export->Export Table to SharePoint List
- In Export table step 1 dialog box, give the address of SharePoint site, check create a read-only connection box, provide a name and description for SharePoint list and click Next
- “Create a read only connection to the new SharePoint list” box is used to keep the excel table up-to-date with the changes made in SharePoint list. This is only one way meaning, you cannot update changes from excel to SharePoint once it is exported. When you check the box, it creates two columns in the excel table
- Item Type – Item/Folder
- Path – folder path for a list item
- In step 2, we can verify the data types of excel columns associated with data types in SharePoint. For example, Student Name of type text in excel is associated with Single line of Text in SharePoint. After verification, click Finish button.
- Table has been published successfully and two columns(Path and Item Type) are created in excel as said previously
- To demonstrate the power of “Create a read-only connection to the new SharePoint list”, let us edit the mark of student, Chitra in SharePoint list
- Then go to excel and click Design->Refresh. Excel table is updated as depicted below
- To remove this read-only connection, click on Design->Unlink
- This approach is useful when we need to check the associated datatypes(shown within braces) before exporting data to SharePoint. If we need to change the data type, cancel step 2, change datatype in excel and follow step 1 & 2 again
- Creating a read-only connection allows to keep excel report up-to-date rather than often exporting updated data from SharePoint list.
If you found this post useful, please press like button, rate this article and share your comments.