his article walks you through a case study that illustrates how to marry SharePoint 2007 with Excel data to offer a Business Intelligence (BI) "lite" solution for avid spreadsheet users. The information and lessons learned in this article come from a real-world project, where a company needed to collect data from hundreds of Excel documents, as well as SQL databases and Word documents, and chose to use SharePoint 2007 to present and "slice and dice" this information.
The project's success showed clearly that BI data doesn't have to come from databases or Excel services; it can come from (arguably) the most popular management and reporting tool in use: Excel.
You'll see some of the challenges involved in creating this project, and explore some options for importing Excel data into SharePoint 2007. The article also provides some sample VB code that can help you massage Excel data into a more SharePoint-friendly form.
The Business Challenge
Discussions with the client determined that their business-critical data (financial information used to manage investments in over 100 companies) was stored in two main areas: a SQL 2000 database that stored data for a financial front-end application, and hundreds of spreadsheets that came directly from the companies in which the client had invested (the "managed companies").
We considered the option of using SharePoint 2007's Business Data Catalog functionality and creating Application Definition files to connect to the SQL 2000 database. The main hurdle was that the SQL data was a little "suspect" and could not be counted on to be 100 percent accurate. However, as the front-end application could easily create Excel reports, qualified professionals could review the data manually, which served as a verification process. With that verification step in place, the data could be trusted, so the first design decision became: Use SQL 2000 data exported to verified Excel spreadsheets as the trusted source of information from the financial application.
After reviewing the Excel spreadsheets that were being used to collect financial data from the managed companies, we determined that one standard template could be modified to hold all the critical data that would be needed to populate the SharePoint 2007 sites. Therefore, the second design decision became: Use Excel spreadsheets as the trusted source of financial information about the managed companies.
So the decision was reached quite quickly that—by hook or by crook—the trusted data needed to come from two types of Excel 2003 spreadsheets per managed company. We would create a SharePoint 2007 site for each managed company (Company A, Company B, Company C, and so on) and then roll up the data to a central site for additional reporting purposes.
A List of Import Options
The next decision involved how to move data from the spreadsheets to SharePoint 2007 lists. In addition to Excel Services (see the sidebar "Excel Services"), which was ruled out immediately, we considered these possible options:
- Cut and paste data from the spreadsheets to SharePoint 2007 lists.
- Use SharePoint 2007's ability to create a list based on the contents of the spreadsheet to import data from the spreadsheets to SharePoint 2007.
- Convert the data in the spreadsheet to a database table and then export to SharePoint 2007.
- Import the Excel worksheets to Access 2007 and then export the Access database to a SharePoint 2007 list.
- Find a third-party product that could import the data to SharePoint 2007.
As the list of options grew and we reviewed the spreadsheets' content in more detail, a couple of things became clear. I'll use some examples and screen captures to illustrate some of the limitations encountered.