Challenges in Importing Microsoft Excel Spreadsheet data to SharePoint

Businesses using Microsoft SharePoint technologies host loads of information that are constantly created, modified, accessed and processed by its users. SharePoint servers store this information in what are called ‘Lists’ that typically resembles an Excel spreadsheet.

As you may be aware already, you can import Microsoft Excel Spreadsheet to SharePoint [In your SharePoint site, select Site Actions -> Create -> Custom Lists -> “Import Spreadsheet” hyperlink under the Custom Lists header]

Microsoft Excel Spreadsheets may do what organizations generally require, but have limitations when the data volume is large or when there are several participants, as may be the case in many organizations.

The following are some of the areas where the users would sometimes feel the pain when importing Microsoft Excel Spreadsheet data to SharePoint 2007:

  • Limited support of data types or data classification
  • Import data to all SharePoint list types, including project tasks, issue tracking, custom lists etc.
  • Missing link between SharePoint and source data
  • Tracking changes to the data
  • Handling complex queries and conditions to filter data

SPList Manager for SharePoint 2007 (SPListM), our SharePoint list management solution helps users overcome these pain points and in this blog post we will explain on how it does it.

Limited Support of data types:

Importing an Excel spreadsheet in SharePoint supports only custom lists and is not compatible with built-in data types such as People, Lookup and Business Data etc. Consider the situation where the user has to retrieve a huge chunk of business data relating to a unique id say Customer ID. All the data associated with Customer ID spread across various spreadsheets has to be referred and interpreted manually. SPListM on the other hand can help users retrieve information across multiple lists by referring to the Customer ID. It retrieves all information associated with the Customer ID and presents it for the user. SPListM supports all SharePoint standard data types. It would be appropriate to term SPListM as a generic SharePoint ‘list processing tool’ as it supports all widely used data types. This feature has managed to attract several SPListM customers and is rightly its value proposition.

Import data to all SharePoint list types:

As said earlier, importing list types such as project tasks, issue tracking, custom lists etc. to SharePoint would be a tough task, especially the ones with file attachments. SPListM offers a simple solution to import metadata (or list items) and associated file attachments to SharePoint lists such as Tasks, Calendar, Contacts, Survey, Discussion Boards, Announcements, Links, Issue Tracking and Custom Lists. For example, SPListM can easily let the user regularly update the project tasks or a custom list through an external batch file and could prove quite efficient by saving several man hours.

Missing link between SharePoint and the source data:

There is only a one-way communication between a Microsoft Spreadsheet and SharePoint services. Changes made to SharePoint list items is restored back to a spreadsheet (content source) while the vice versa is not possible. If the changes made to the spreadsheet source are to be updated in SharePoint list, it has to be exported to SharePoint as a new list. The link between the content source and the destination (SharePoint list) is terminated and this could prove costly when data undergoes constant changes. Consider a situation where loads of information is constantly dumped into an Excel spreadsheet and needs to be updated in the SharePoint site. It would be increasingly difficult for the user to export it every time or update the voluminous information manually in a datasheet view. SPListM could prove extremely handy in this situation as the updates could be defined in the import task wizard or through an external metadata file. SPListM does not necessarily establish a two-way communication with the SharePoint. But it still makes a one-way communication complete and more effective.

Tracking changes to data (versioning):

It is increasingly difficult for the user to keep track of the changes made to the content sources as the changes made would only be exported to SharePoint as a new list. As a result of which, ‘versioning’ is made impossible. In situations where a list has to be approved by other SharePoint users, it would be impossible to keep track of changes made by each user. Updating a list item with SPListM can help you maintain versions and track changes made by users in a collaborative environment.

Handling complex queries and conditions:

It would prove a daunting task for the user while attempting to conditionally import data using complex queries from a spreadsheet to a SharePoint site in an automated manner and more so if it involves a lot of data. In a situation where data is often exported to a spreadsheet and the user has to migrate the same to a SharePoint site, Excel’s filter options could be very useful. But what if the user wants to impose conditions at various levels and do a major cleansing of data? SPListM has the ability to impose conditions at various levels and could do this cleansing as easy as a cake walk. It allows the user to apply multiple logical conditions and can update the list items with ease. The updating could be done either through the basic import task wizard option or through an external batch descriptor file.

SPListM has been engineered in such a way as to fill the potential pitfalls a user faces while importing Microsoft Excel spreadsheet data to a SharePoint list. SPListM supplements Microsoft Excel’s capabilities in enhancing business productivity.

To try and evaluate SPListM, you can download a 15-day trial version at