How to Import Excel Spreadsheets into a SharePoint List

Here is an example that shows in detail how you can import an Excel spreadsheet into a Microsoft Office SharePoint Server (MOSS) 2007 List. I used my Windows Networking User Group Web site as an example because I have easy access to it. I am using Excel 2007 in my example.

First create an Excel document with column headings and data that needs to be imported, if one doesn’t exist already. To import an Excel file into SharePoint, you don’t have to first create a List and all the columns because when you import an Excel spreadsheet, SharePoint will create all the columns and will also create the list based on the name you provide. If you use column names that already exist (e.g. Name), SharePoint will create a column with a number appended to it, e.g. Name1. Here are all the steps.

1. Go to Site Actions, View All Site Content.

2. Click Create.

3. In the Custom Lists section click Import Spreadsheet.

4. Enter the Name, Description and File location, where:

Name: This will be the name of the new SharePoint List
Description: An appropriate description of the SharePoint List
File location: The path to your Excel file

Click the Import button.

5. You will see the Excel spreadsheet open up. In the Range Type section, click the drop-down button and select Range of Cells. Click anywhere in the Select Range box. This will allow you to select the text that you want to import into SharePoint List.

6. Highlight the headers and all the cells with your mouse (click and drag) and then click on the icon on the right-hand side in the minimized Import to Windows SharePoint Services list. This will allow you to get back to the mode where you will be able to click the Import button.

7. Go ahead and click the Import button to start the import process.

NOTE: Whether you are using Excel 2003  or Excel 2007 there is a chance that you may see the following error:

Method ‘Post’ of object ‘IOWSPostData’ failed

If you get this error, don’t panic. Just read my blog post Error: “Method ‘Post’ of object ‘IOWSPostData’ failed” in SharePoint on how to work around this error.

8. If you are prompted for logon credentials, provide the account name a password that has permissions to add to the SharePoint List.

9. You will see your Excel spreadsheet imported into SharePoint with the name of the list that you provided in step 4.

NOTE:

If you used a name for the column that already exists in SharePoint, e.g. Name. SharePoint will append a number 1 to the column title, e.g. Name1. Next time it will use Name2, and so on.

Conclusion

As you can see, adding data from Excel spreadsheet into SharePoint is relatively straight forward. You should check out this blog post from my colleague Sharee English Excel and SharePoint: Part 2 because she prefers the Named Range as the Range Type and shows a slightly different method to import data from Excel into SharePoint. With Excel 2003, or even Excel 2007, you may run into the Method ‘Post’ error that I mentioned earlier. The solution I posted in another blog post seem to work for just about everyone. I’ve run into this error on several computes both Windows XP computers running Office 2003 and Windows Vista running Office 2007 and was able to fix the problem with this solution.

Copyright ©2009 Zubair Alexander. All rights reserved.