Do you have a huge excel file with at least 20,000 lines of data and no idea how to convert it to a SharePoint list? Then you are at the right place to easily export and import it to SharePoint using Microsoft access tutorial. Usually excel files have a lot of columns, fields and a large amount of data in it which makes it hard to replicate to any alternate formats.
Converting excel manually to a SharePoint list is practically impossible because it takes a huge amount of time. Microsoft Access database management system has a very dynamic feature to import the excel files into it, make necessary changes and export it directly to a SharePoint list in very few steps. This article will guide you on how to import an old SharePoint list converted to an excel file that can be reimported as a SharePoint list using Microsoft Access.
A close look on Excel, Access, and SharePoint
You already know that Excel is a spreadsheet software developed by Microsoft which consists of several features like calculation, graphing tools, pivot tables, etc. Those who are familiar with excel know its 484 functions including data manipulations and arithmetic operations.
On the other hand, Microsoft Access is a database management system that combines the Microsoft Jet Database Engine with a good graphical user interface that can manage data and information pretty efficiently than Excel.
Apart from that, SharePoint is a network-based collaboration platform that integrates with Microsoft Office to provide solutions for various office tasks. It a very efficient tool for online document management and storage.
In this article, we are trying to combine the features of all the three tools to import data to SharePoint lists with minimum effort and maximum productivity.
What is the need for converting Excel to SharePoint list using Access?
In certain use cases like an old SharePoint list can be migrated to online by exporting the old file to excel. By this technique, the old version can be easily converted to excel. In the case of very large excel files, these exported files cannot be directly imported as SharePoint lists. In such situations, you can first import the old list data to an excel file, then import the excel file to MS Access and then export the data back to a new Sharepoint list in the online version.
How to import tables from Excel to SharePoint list is done.
- First, install Microsoft access on your personal computer.
- Make sure that all the old SharePoint lists are converted to respective excel files and stored on your machine.
- Open MS Access on your machine and create a new database and name it accordingly.
- Create a new table on the newly created database.
- Right-click on the new table “Table 1” and select “import excel”.
- Now select the excel file you want to import from the machine and click open.
- Save this to your database and give the document a different name “Table 2”.
- Now double click on the table containing data and verify all the contents are imported correctly.
- Right-click on Table 2 which contains data and select export to transfer the data to the SharePoint list.
- Give the URL of the Sharepoint site, name of the list to be created to export the data. Now enter the credentials of your site and click import.
- Now the data will be successfully exported to the newly created SharePoint list.
The main advantage of this technique is to convert a very large old SharePoint list or excel file to Sharepoint online without data loss or data corruption.