Import Excel files to SharePoint 2010

Sometimes we have a need to transfer Excel spreadsheet to the SharePoint 2010 environment.
Because Excel is very popular business application, I see quite a few spreadsheets out there that are used for managing complex business operations.

At some stage the decision comes up to move Excel-based system to SharePoint for certain good reasons and business users try to upload their spreadsheets to SharePoint lists and libraries.

It might seem obvious that we could use out-of-the-box functionality provided by Microsoft in SharePoint 2010 which is "Import Spreadsheet" which "creates a list which duplicates the columns and data of an existing spreadsheet".
What it does in reality is: allowing you to select a range of cells, creating a list with all columns as single or multiple lines text. That's it.
So for real life scenarios we need a different approach to import Excel data to SharePoint appropriately.

In the scenario here we have got a simplified application management system in Excel that features two related worksheets: Applications and Environments. Applications is a list of software and Environments is a list of servers and owners where the software exists.

 

and

 

It's clear that this example is just for demo purpose and we don't have all the details etc., but what it will show you is how you will be able to preserve relationship between worksheets and create all the relevant lists linked together in SharePoint with all the relevant data types that you might need during import so your system will be easy to use in SharePoint as well.

The Solution

It might not be the most efficient one, but I know there is a lot of companies out there who don't want to use third-party tools, but like doing everything themselves. So this will help someone who is keen to import Excel data properly into SharePoint without using any third-party tools.
Anyway we will assume that you have Excel 2007 at least, SQL Server 2008 including developer tools and SharePoint 2010.

1. Save your Excel worksheets as separate Text (Tab delimited) .txt files. So we have got Applications.txt and Environments.txt
2. Create a table in SQL Server for each worksheet. Make sure the primary key columns are of type INT and are IDENTITY columns. That will help us further in importing data properly.
Here are the tables we have created for our situation - Applications and Environments:



3. Create an SSIS project in Visual Studio 2008.

3.1. Select Business Intelligence Projects as a type for new project and an Integration Services Project as a template:

 

3.2. Add a Flat File Connection to the Connection Managers area:



3.3. In the settings specify the file and columns advanced settings. Make sure you choose Unicode string [DT_WSTR] for any string data you expect from Excel and also make sure the length of column and data type are exactly the same as in database:





3.4. Drag-n-drop Data Flow Task to the Control Flow window from the Toolbox:



3.5. Double click the Data Flow Task to see the next screen and then drag-n-drop Flat File Source :



3.6. In the Flat File Source properties (double-click on in to see) specify the connection we created at 3.3.

3.7. Drag-n-drop OLE DB Destination from the Toolbox:



3.8. Link together Flat File Source and OLE DB Destination with the green arrow:



3.9. Then specify the database, table name and set up mappings for columns (Input Columns -> Destination Columns):

 





3.10. Run the package and make sure you don't have errors.
 


Check the result by looking at the SQL Server table:



For the process to run smoothly I suggest to import lookup tables first. In our case it is Applications. Then we will import tables that refer to lookup ones.

3.11. Add new SSIS Package or create a new project for the next worsheet import. Go through steps 3.2 - 3.6. to set up the file source. Then drag-n-drop Lookup transformation from the Toolbox:



3.12. In the Lookup properties specify the lookup table (in our case it is Applications). What we are going to do here is to find records in the lookup table using name to get the ID which will then be used as foreign key:



3.13. We map the column from the file we want to use as a lookup string and then map it to the lookup column in the table by drag-n-drop.
Then we tick the checkbox near ID column so it will be added to our output for the records that will be found in the lookup table using the string:
 


3.14. In the error output settings we say to ignore if we can't find the record in the lookup table:



3.15. After we connect the Flat File Source and Lookup transformation we will select "Lookup Match Output" as output from file source:



3.16. Drag-n-drop OLE DB Destination and connect it to the Lookup transformation:



3.17. Specify table and column mappings. For the foreign key column specify the ID column that was added in the Lookup transformation settings. In our case it is ApplicationID -> ApplicationID:



3.18. Run the package and make sure it all went well:





4. Now it's time to import those database tables to SharePoint.

4.1. First create a list for each table and put the lookup columns where reauired:

Applications



Environments



4.2. Application column is the lookup column to the Applications list and we use Title column for that:



4.3. Create SQL queries that will mimic the display names of the SharePoint columns for each table:



4.4. For lookup columns in our case the values returned by SQL query should be in the "ID;#string" format. So we join the main table and lookup table for that:



4.5. Create a PowerShell script that will grab the SQL query result and transform it into SharePoint list items using API:

$listName = "Applications"
$sqlQuery = "SELECT [Name] as [Title],[Description],[SharePointSiteUrl] as [SharePoint Site Url] FROM [Applications]"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
$SqlConnection.ConnectionString = "Server=ServerName;Database=dbname;Integrated Security=True"
$SqlCmd.CommandText =  $sqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$dbs = $DataSet.Tables[0]

$site = Get-SPSite http://win-vrqsubldsl2
$web = $site.OpenWeb()
$list = $web.Lists[$listName];
foreach ($row in $dbs.Rows) { $item = $list.Items.Add(); foreach ($col in $dbs.Columns) { $item[$list.Fields[$col.ColumnName].InternalName] = $row[$col] } $item.Update(); }
 
$SqlConnection.Close()

4.6. Run the SharePoint 2010 Management Shell on the SharePoint server and execute the script that have just been created.

4.7. Check the list has been imported propely:



4.8. Change the PowerShell script parameters to include the right name and the right SQL query for the next list and run the script.

4.9. Check the list has been imported properly:



4.10. Check lookup column values:



5. Enjoy!!