“In our organization, we deal with lots of customer data and analyze their behavior patterns to suggest them the suitable products for their businesses. But after analyzing the data, we want to store them in the SharePoint lists as we need to share them with other departments or analyze them again. The data is present in the large-sized excel sheets. How can we accommodate these sheets in the SharePoint? We don’t want to disrupt the arrangement of the columns as they are important for the analysis and results.”
The above statement is of my friend, who is a Data analyst and work on Excel sheets consistently. But he is not aware of the SharePoint environment and how does it support the import of Excel Sheets to SharePoint. So, in the current article, we will go through the various methods which allow you to import MS Excel to the SharePoint list.
In the quick edit option in the list of a SharePoint, you can input the data from the Excel spreadsheet directly to the list you have created earlier. You need to create the columns similar to the columns of the spreadsheet.
Log in to your SharePoint site and create a list (or select an existing list). You need to create columns in the same manner as in your spreadsheet. Because you will copy the whole rows to the list, and any mismatched data will create a problem for you.
You can click the ‘Add Column’ and choose among the option to create a suitable column.
After creating the column, click the ‘Quick edit’ option.
The list columns are open to receive the data. Copy the row from the excel and paste it in the list row.
The simple copy-paste process will bring the whole data to the SharePoint list.
NOTE:
It is a time taking process where you need to copy-&-paste whole rows from your spreadsheets. If the size of the spreadsheet is large and the number of rows is higher, then the process may take a lot of time.
If you copy any row incorrectly, then it will be harder to find out the error.
There is another method that involves using the ‘Import spreadsheet’ web part option where you can directly import the Excel sheet to the site. Here is the process;
Click the Settings gear option on the site page.
Select the option ‘Add an App.’
Choose ‘Import Spreadsheet’ in the App list.
Provide a name to the spreadsheet, a small description, and the File location. Browse the file from its location and click Import.
The Excel file will open up where you need to select the range of cells that you want to import. Select them and click Import.
The table will be copied to the SharePoint site list. The table will have proper columns and rows as it was saved in the Excel.
NOTE:
The process works only at the Internet Explorer, and if you are using any other web browser, then it will give an error message that ‘This feature requires a browser that supports ActiveX controls.’ So, either you perform this method only at Internet Explorer.
If there is a drop-down list, then its entire content will be copied as the text. So, the method may disrupt the arrangement of your data.
In the third method, you can directly export the table to SharePoint list without any external assistance.
Open the MS Excel sheet, which you want to see at the SharePoint list, then choose the option ‘Format it as a table.’
While the table is highlighted, go to the Export option and select the ‘Export table to SharePoint list.’
In the Address field, input the link of the site of the SharePoint list. Provide a Name and description to the sheet and click Next.
The next screen will present all the columns and their respective data types. Click Finish.
A message will appear that showing that the table was successfully published and the link where you can view the table at the SharePoint list.
The table is presented in Datasheet view. So, to see a normal list, you will need to click the Stop Editing option. Also, note that a classical list is created rather than a modern one. And if you had a drop-down list, it will be saved as text only.
Tips to manage imported excel items into SharePoint lists
Once you have imported columns from the excel spreadsheet to SharePoint lists, then you can start to access the data instantly and analyze them to retrieve desired results. We are mentioning to you some important tips that you should use to alleviate the performance of the list’s data-
Enable versioning in the list – Versioning lets you check the older version of the same file easily. If you have imported some columns badly or mistakenly, then you can restore them to a previous version of the list.
Approve list items – The SharePoint list items remain pending until the administrator approves them that who can access them. You can control the users who can access the imported data.
Manage the permission levels – The Administrator should change the permission levels of the user as to who can only view the items and who can edit them.
Create a different view of the same list – You can change the view of the same list for different users. The contents of the list will remain the same, but the view will be organized differently for different users.
On all 3 manual methods, you will check that it will work for a single list or a excel file. When you have multiple spreadsheets that are required in different SharePoint lists, then you will have to run each method multiple times. To easier migration and time saving, you can use a professional software that can imports excel files to SharePoint lists and manage all the properties, permissions, and versioning too. Kernel Migrator for SharePoint will import unlimited data empowered by several helpful features.
You must checkout the following benefits of Kernel Migrator for SharePoint that make it a worthy candidate of importing MS Excel data into SharePoint lists-
Its user-interface is pretty direct and easier enough to import larger datasheets.
It supports all kinds of Excel application’s formats including the CSV.
It can import attachments too.
Before importing the data, it can apply date filter on the selected excel files.
It applies several kinds of filters to choose only the important spreadsheets.
It checks and removes already migrated items.
It can schedule the import at a comfortable time.
It can import excel lists to single or multiple SharePoint accounts and groups.
Conclusion
All the above three methods present simple processes to export an Excel sheet to the SharePoint list. Though it is easy to export an Excel sheet to a SharePoint list, this is not the case with other data on file systems. When there is a large amount of data, you need a professional SharePoint Migration tool. If you want a secure migration, then you should use Kernel Migrator for SharePoint tool. It not only migrates the data but also protects it from any change or editing. Multiple filtering options allow you to include the important data and leave the rest. After completing the migration, you will also get a migration report.
|
Updated On - December 08, 2022 |
I was able to import my Excel to SharePoint list. using this method, you provided a simplest solution. Thanks!