Two ways to import an Excel table to a SharePoint list
An Excel tutorial by Peter Kalmstrom
 In
the demo below the kalmstrom.com CEO Peter Kalmstrom shows
two ways to import data from an Excel table to a SharePoint
list.
As an example Peter uses a company contacts list for staff
that is already present in Excel. This Excel list has a
link to a photo of each staff member.
Peter uses Excel and SharePoint 2013 for his demo, but the
two methods are avaliable in earlier versions too. For both
methods he must first format the Excel list as a table.
The first method Peter shows is to export the table
to a SharePoint list with the Export button in Excel. This
method creates list columns in SharePoint.
The resulting list is not very user friendly, but Peter
shows how to modify it so that it works well. However, with
this method the photos will still be links.
The second method is more complicated but also much
more powerful. Now Peter begins in the other end, in SharePoint,
and he also uses Access. That way he can control what columns
will be used.
Site columns give several advantages, for example in searches,
so Peter prefers them to list columns. The second method
makes it possible to use site columns and also to display
the photos directly in SharePoint.
This method can be used in the same way for CSV files.
These are the steps in the second method:
- Create a Contacts list in SharePoint.
- Modifiy the default Contacts list
- Add a Contact Photo column from Site columns
- Add a Picture library
- Upload photos to the Picture library
- Change the links in the Excel table Picture column
so that they link to the photos in the Picture library
- Add the Departments to a new Department choice Site
Column
- Add the Department column to the Contacts list
- Open the Contacts list as a table in an Access database
- Link the Access database to the Excel file by creating
a linked table
- Create a Query Design that appends the linked list
table to the Contacts list table
- Run the Query
|
