1

I am working on a sharepoint server 2016 on-premise server. and our customer have asked us if there is a way to import excel sheets into sharepoint sites. now i find this quick and easy way :-

  1. add an App of type "Import Spreadsheet", give it the same name as the excel sheet.
  2. browse for the excel sheet from my PC and click on import.

where the results will be as follow:-

  1. a new custom list was created.
  2. all the excel sheet columns were created as list columns.
  3. no content type was added to the list.where all the list columns were added directly inside the Item list content type

now i got a quick list created in almost no time and no effort.. but i have been working on SharePoint for around 4 years, and i always do the following when creating lists:-

  1. create all the columns as site columns. since site columns can be re-used on multiple lists. while the "Import Spreadsheet" will create the fields as list columns. also site columns will have managed properties created automatically for them unlike list columns.
  2. i always group my custom site columns inside custom content types. and i always have a hierarchy between the content types to ease re-usability. while when using "Import Spreadsheet" no content type will be created.

so can anyone adivce on the below 2 points:-

  1. should we depend on the "Import Spreadsheet" to import excel sheets as lists inside sharepoint? keeping in mind the limitations i mentioned above?
  2. will it be a better appraoch to manually create an empty list . where i can manually define site columns which represents the excel sheet columns, and group my custom site columns into site content types and reuse existing custom columns if applicable? then using the quick grid to copy/paste the excel sheet data inside the new list..

i am really confused if using "Import Spreadsheet" should be prevented or not? i can see it will provide a fast way to import existing excel sheets and data,, but at the same time it will provide poor design since it will always create list columns which can not be reused and also will not create and arrange columns inside content types,which is considered a bad design in sharepoint...so can anyone adivce on this please?

2

Not using content types is considered bad design, but a lot of people have created a lot of solutions without them, and the simple truth is that there are many scenarios where not having content types works out just fine.

So, if you want a polished solution that includes the full range of SharePoint functionality, then do what you suggested and create the list properly and copy in the data. If, however, a user has a single table in excel and they want to get it into SharePoint quickly, then the import wizard is a nice way to do that.

As an example, I was working with a user recently that had an excel spreadsheet who liked the idea of having it in SharePoint to get access to the alert capability, but they were new at SharePoint and didn't know or care about things like setting up rules to route documents based on a content type. For them, the import wizard is the tool they were expecting to find, as they would have been confused if there was no simple way of getting excel data into SharePoint.

  • 1
    I agree with all your points. But I also know from experience that "content types" is a difficult concept for a lot of people to grasp. People who work with SP should know it, of course, but many people just want to use SP without having to know it, if that makes sense. So if people only want to import one spreadsheet, don't want or need the complexity of lookup fields or any other complex data type, and don't know about or want to use Search parts, then the import utility is fine. Granted, those are a lot of conditions, but again, a lot of people are overwhelmed by the complexity ... – Mike2500 May 3 '17 at 1:50
  • 1
    and possibilities that SP provides, and are perfectly happy with the simple solution. – Mike2500 May 3 '17 at 1:51
  • 1
    Or, let me ask this question: let's say a person imports an excel sheet, and creates a SharePoint list that is well used and well liked by their department. People use views and alerts to interact with the data. Everyone agrees that the list provides a nicer experience than what they had before. Should we criticize that person for setting up a non-optimal solution? Personally, my approach is to congratulate them, and then simply point out that there are even more possibilities available, if needed. – Mike2500 May 3 '17 at 1:55
  • 1
    Yes, you can create a a managed property for a list column, though they are not created automatically like they are for site columns – Mike2500 May 3 '17 at 11:43
  • 1
    ....so i will have to create the managed properties manually... also our customer might ask us to add new choice inside the let say Status column,, then instead of modifying the Status site column and have the effect on all the list columns i will end up doing so on each list separately. I am not sure why i can not find a robust solution in SP. For example i can image if SP would have provided a way to import excel,, then allow us to map the excel sheet columns to existing site columns,, or chose to create new site columns .... :) – john Gu May 3 '17 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.