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. If you are a trainer and would like to use the file I’ve created for demo to your students, you can download this Excel Sample File. The file is in Excel 2007 format.
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.
April 10th, 2009 at 6:49 am
Nice tutorial, but it should be mentioned that importing spreadsheets doesn’t work if you are using IE7, or a non-Microsoft Browser (from the MSDN Website)
March 9th, 2010 at 7:55 am
Very detailed and helpful! Thank you!
June 30th, 2010 at 10:02 pm
Nice tutorial. Doesn’t help me though because I want to import 3 spreadsheet files in 3 different EXISTING lists.
Any ideas? Thanks.
September 22nd, 2010 at 11:49 am
I tried your fix but my machine will not allow enable macros, it is disabled, then when I try and save it removes signature. I am warned when I try and open the file that the signature is invalid. I have tried opening this file on several different machines and I get the same results as mentioned above.
Interestingly on the machine I need it to work on the exptoows.xla file is in a different location, even after I loaded office 12 on the machine. I have several problems here.
September 22nd, 2010 at 11:59 am
Denson,
You didn’t mention what version of Office you were using but you can try this solution from Microsoft for your invalid signature problem:
“The “Invalid signature” indication appears when you try to open a Word document or an Excel workbook”: http://support.microsoft.com/kb/967732.
September 22nd, 2010 at 12:17 pm
Thanks for the response, I actually found what was wrong.
It appears that the dev box wasn’t setup properly, I deleted/recreated the main Web application and added a site collection to the root. Just glad this issue was not on production. That was the problem all along. I forgot where I found the fix. Strange that this configuration would cause this problem.
By the way I am on Office 2007. Also the exptoows.xla file was not in the 1033 of office12. Go figure.
Again Thanks for your reply.
September 22nd, 2010 at 12:19 pm
I am glad you found a solution.
October 12th, 2010 at 12:39 am
Whenever we try to import the file we’re getting the the error “The specified file is not a valid spreadsheet or contains no data to import”. I even get this error after downloading and using the Excel Sample file.
Do you think that this might be a configuration error?
Appreciate your help.
Sheila.
October 13th, 2010 at 6:36 pm
Sheila,
In IE, go to Tools, Internet Options, Security tab, Trusted Sites, Sites, and add your site. Hopefully that should fix the problem.
February 16th, 2011 at 7:14 am
Having the same problem as Sheila. My site is already a trusted site, still receiving the same problem.
June 29th, 2011 at 11:18 am
Thanks for the great tutorial. I should have checked this site first. I have my spreadsheet in SharePoint now in datasheet view however the formulas are not working. I have a column, for example, that tabulates the sum of several preceding columns. I can click on the cell and enter a value, but the macro does not then tabulate the sum. In SharePoint do I have to enter the updates in the Excel spreadsheet and then reload it to the SharePoint site or should the datasheet view preserve the formulas?
Thanks.
June 29th, 2011 at 12:33 pm
Ryan, you cannot make changes to the cell in datasheet view if you are working with a calculated column. The moment you enter a value you are essentially overwriting the calculated field. Make the change in standard view and then switch to datasheet view.
July 25th, 2011 at 12:08 pm
Thanks for the instructions it helped. But let people know that they have to have a blank row at the top and a blank column to the left.
July 25th, 2011 at 12:17 pm
I’m having the same problem Jeanette is. My site is already a trusted site, but I’m still getting the “not a valid spreadsheet” error.
July 27th, 2011 at 10:07 pm
Hi sir,
While following given steps I am getting an exception after clicking on import button-
“An unexpected error has occurred(-2147467259)”
Please help. What can I do?
September 13th, 2011 at 11:36 pm
Getting error “The specified file is not a valid spreadsheet or contains no data to import”
Please tell what to do my site is already a trusted site.
September 15th, 2011 at 8:58 am
I have a very large file that I’m trying to import. When I enter the cell range $A$1:$BS$2733 into the Range of Cells box I get a TYPE MISMATCH error. I’ve tried different variations of the formula to no avail. What am I doing wrong?
January 26th, 2012 at 6:20 am
Once you create a SharePoint list, is there a way to append it with weekly updates while keeping the previous entries? Is there a way to do this automatically? Thanks.
January 26th, 2012 at 6:26 pm
Hello, did anyone identify a solution to the below? This is my situation too. Thanks.
3. Neil Says:
June 30th, 2010 at 10:02 pm
Nice tutorial. Doesn’t help me though because I want to import 3 spreadsheet files in 3 different EXISTING lists.
Any ideas? Thanks.
January 28th, 2012 at 7:30 pm
Neil and Doris, check out this CodePlex solution for importing Excel data into existing SharePoint lists. This was meant for MOSS 2007 and I haven’t had a chance to try it in SP2010. Here’s the URL: http://spreadsheet2splist.codeplex.com/.
Akaalx, I don’t know of an out-of-the-box solution but you might want to look at this Business Data List Connector for SharePoint which connects SharePoint’s native list to external business data sources. It offers bi-directional connection and updates: http://www.layer2.de/en/products/Pages/SharePoint-Business-Data-List-Connector.aspx.