Home >Tips > SharePoint Online from Scratch > Use Access to import Excel to SharePoint kalmstrom.com site map icon Site map  

Use Access to import an Excel table to a SharePoint list

A SharePoint Online tutorial by Peter Kalmström

SharePoint iconIn 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.

Finally, when you use the Access method to import data it is possible to get the new experience. For all these reasons, and if the Access method is more complicated, I would always recommend it.

These are the steps to export an Excel table to SharePoint via Microsoft Access:
 
  1. 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.

  2. Under the LIST tab, click on 'Open with Access'. A new database is created. Select the option 'Link to data on the SharePoint site'.

  3. In Access, click on the Query Design button under the CREATE tab.

  4. 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'.

  5. Check the box for headings if your Excel table has headings.

  6. Give a name to the new, linked table with your Excel data.

  7. Access icon Drag the Excel table into the Query field.

  8. Under the DESIGN tab, click on the Append button.

  9. Select to append to the SharePoint list you have opened in Access, and click OK.

  10. 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.

  11. Append the Excel columns to the corresponding columns in the SharePoint list.

  12. Run the query by clicking on the Run button under the DESIGN tab.

  13. 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.





Learn more


---------------------------------------------------------------------------------------






Always the latest news in the kalmstrom.com blog



Follow kalmstrom.com on LinkedIn Facebook, Google+  or Twitter!

back icon next icon