SharePoint – 2 Mistakes to avoid when importing Excel data
January 10. 2021
At some point, many app builders want to use the SharePoint feature to import Excel data into a new list. There's a 'knack' to doing this and in this post, I'll describe the settings to make this process as smooth as possible.
A common, frequent requirement for app builders is to import an Excel spreadsheet into SharePoint. A typical use case is where we use Excel as a data source, we reach the delegation/data size limitations of Excel, and we need to migrate to a better data source.
The simple go-to method is to use the import spreadsheet option in SharePoint. In this post, I’ll highlight the two common mistakes that happen, and provide a brief walk-through of the process.
Mistake 1 - Not correctly preparing our Excel spreadsheetThe first thing is to make sure that the columns and cells in Excel reflect the correct data type. For example, we should make sure to format any numeric values in our spreadsheet as a number, to prevent the import process from importing the column as text.
Also, we should remove any columns that we no longer need. For instance, if we've connected to our source spreadsheet from Power Apps, we should make sure to remove the auto generated __PowerAppsID__ column that Power Apps adds.
Although we can correct these data issues post-import, it generally takes less time overall if we invest some time upfront to make sure that the source data is as clean as possible.
These steps are crucial! The import process works with Internet Explorer only, so we must make sure to use that browser.
Mistake 2 - Not using IE, and not configuring the settings correctly
In addition, there are 2 VERY important IE settings that we must apply:
- We must add the our target SharePoint site into IE's trusted zone
- We must untick the "check for server certificate revocation" checkbox in the Advanced > Security section of Internet options.
Now that we've applied the necessary IE settings, here's a walkthrough of the import process.
The first step is to select the New > App menu item from SharePoint.
From here, we select the Import Spreadsheet option. A security dialog will appear, which we should allow.
In the next step, we specify the name of our new target SharePoint list and we browse for our target spreadsheet file. Note that if we didn't add our SharePoint site to IE's trusted zone, we'll receive the message "The specified file is not a valid spreadsheet or contains no data to import.", as shown beneath.
Next, Excel will open and we can use dialog to select our source Excel data. We can then click the Import button to import the data into our new list, and to complete the process.
If the import button fails to work, the most likely culprit is that the "check for server certificate revocation" setting is causing Excel not to authenticate correctly to SharePoint. If there is still an authentication problem, we should make sure that sign-in prompts are not blocked from the settings in Excel's Trust Center.
In this post, we walked through the import Excel feature in SharePoint. When we use this feature, we must make sure to use Internet Explorer, add the our SharePoint site to IE's trusted zone, and to disable the option to check for server revocation.
- Data - Combine columns from separate tables into a single table
- Formula - Transposing/converting rows to columns- an almost impossible task?
- Data - How to rename field names in a record
- Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
- Data - Retrieving news/forum/blog articles with RSS
- Data - How to sort by partial numbers in a text field
- Data - How to return the last record from a table
- Data - How to create bulk test/dummy records with random values
- Data - 3 things you should know before using the MySQL or PostgreSQL connectors
- Data - A walkthrough of how to migrate the data source of an app from Excel to Sharepoint
- Data - How to enforce unique values (or prevent duplicate values) in one or more columns
- Data - How much mobile data does Power Apps consume? What ways can we minimise this?
- Data - How to save and retrieve Google calendar entries
- Data - How to save and retrieve Google contacts
- SQL - Caution! This is how users can hack shared SQL connections
- SQL - Don't let this DateTime bug catch you out!
- Settings - What's the purpose of the "Explicit Column Selection" Setting?
- SQL Server for Beginners Part 3 - Installing On-Premises Gateway
- SQL Server for Beginners Part 2 - Installing Management Studio
- SQL Server for Beginners Part 1 - Installing SQL Server
- Searching data–What you need to know about case sensitivity
- Images - How to create images that can change depending on data
- Excel - Reasons NOT to use Excel as a data source
- SharePoint - What you need to know about Filtering Data
- Formulas - Generating Row Numbers