Import Excel Data via Access
A SharePoint Online tutorial by Peter Kalmström
 In
four earlier demos Peter Kalmström, CEO and Systems Designer
of kalmstrom.com Business Solutions, has discussed Excel
versus SharePoint and showed
how to export an Excel table to a SharePoint list using
the Export button in Excel. This is a quick method, but
it has some disadvantages.
The Access method is more complicated, but it gives you
a better control over how data is added into the SharePoint
list. You can decide exactly which Excel columns you want
to include in the SharePoint list and how data should be
distributed. You can also use an existing list with site
columns for the import of Excel data.
Another advantage of the Access method is that changes can
be made in both Access and SharePoint. When you enter data
in Access it is saved to the SharePoint list as soon as
you move to another row. When you enter data in SharePoint,
the linked Access table will be updated next time it is
opened or refreshed.
These are the steps to export an Excel table to SharePoint
via Microsoft Access:
- Create a new SharePoint list. Start with a standard
app and modify it to fit your needs. If you want to
add columns, Peter recommends you to use existing site
columns. They give several advantages, for example in
searches.
- Under the LIST tab, click on 'Open with Access'.
A new database is created. Select the option 'Link to
data on the SharePoint site'.
- In Access, click on the Query Design button under
the CREATE tab.
- Click on the Excel button under the EXTERNAL DATA
tab and browse to the Excel file you want to use. Select
the option 'Link to the data source by creating a linked
table'.
- Check the box for headings if your Excel table has
headings.
- Give a name to the new, linked table with your Excel
data.
-
Drag the Excel table into the Query field.
- Under the DESIGN tab, click on the Append button.
- Select to append to the SharePoint list you have
opened in Access, and click OK.
- In the Excel table in the Query field, double click
on the names of the columns you want to include in the
SharePoint list. They will then show up in the grid
below, so that you can append them. You may also drag
and drop the column names to the grid.
- Append the Excel columns to the corresponding columns
in the SharePoint list.
- Run the query by clicking on the Run button under
the DESIGN tab.
- Now when you go back to the SharePoint list and
refresh it, you can see the Excel data distributed in
the list columns in the way you mapped it in Access.
|
