Importing Spreadsheet To SharePoint List Gotchas – And What We Learned
SharePoint is a platform that helps improve the way we collaborate. It is a powerful and useful tool for small to large teams, to enterprises and established businesses. A content management tool with document sharing capabilities that can also be used for communication, as well as, automation of business processes through its workflows.
It is versatile and can be used in a number of ways with its many integrations. With these said, it is a rather complex product that it is sometimes inevitable to encounter strange and hard to explain issues with it. There are these little things that can run into while using it that will make you scratch your head. One is with importing an existing spreadsheet to a SharePoint list. We had this experienced in an on-premise SharePoint implementation.
One can save time creating a SharePoint list by importing an existing spreadsheet. The headers become the list columns, where the rest of the data are imported as list items. You basically just have to create a list and select the Import Spreadsheet app. Enter a name for the list and select an existing spreadsheet. Select the rows you want to include in your list and click Import.
Doesn’t seem so hard, right? Well it shouldn’t have, unless you experienced this error we got. An unexpected error has occurred. Your table cannot be published. It doesn’t also help that the description of the error is so vague.
Usually these type of errors point to a configuration on IE settings. We checked that the required add-ons are enabled and the site is included on the Trusted sites. Still got the error. Maybe an issue with the installed version of the Office? Repaired it and that did not help either.
It also did not help that we got inconsistent results with all the other workarounds we tried. We tried using Export To SharePoint option in Excel but that failed. We tried importing using a table, named and cell range but though we had times when it worked, we failed to import most of the times. We even tried to embed a data connection from an existing list that we exported to Excel and used that to import. That worked a couple of times but not all the time.
We ruled out the possibility that the problem is in the spreadsheet file. We are using the same spreadsheet and it sometimes work, sometimes did not, unfortunately. Problem with data connection, and session is also not something we can potentially include in the possible causes either, due to the same reason we had above.
There are just times that it did not work. What’s up with that? This got us to thinking that this could be a process conflict, given the timing issue. We head over to the logs and see if there is something there that will give more details to this error that can help us understand more. There are some update conflicts that made us look into the jobs status. We found out that we encounter the error when the timing when we import the spreadsheet is at the same time this specific job is running.
We were able to identify the issue after a lot of head-scratching and lots of testing. Hope this could help those who are experiencing the same issue save some time.
Send us your questions on SharePoint
Related Blog Post
The Joy it Gives
This blog has been quiet for over a year. Of course, I have to write my first post after a long time while experiencing flaky internet. Such has been the last few months for us - a lot of changes sometimes wanted and some surprises. Two of our For Dummies books...
SharePoint Cross-Domain Library Part 1: Getting To Know The Library
We have attempted to have an in-depth understanding of how permissions work in SharePoint add-ins in a blog post here and have mentioned a thing or two about the need for the cross-domain library when doing cross-domain calls. When you have for example built an app...
Customizing SharePoint Lists Using Column Formatting
Now available on "modern" SharePoint lists, column formatting is one way of applying custom formats to columns. Pretty much similar with the conditional formatting in Excel, column formatting makes it possible to apply styles to columns like CSS or modify the way data...