May 9, 2012

Import Excel Sheet to SharePoint List

In this article, we will learn how to export the excel data to the SharePoint list.
1. Click on Site Actions -> More Options 
2. Now click on the Lists -> Select Import Spread Sheet from the type -> then Click Create 
3. Give the Name and Description of the List to be created and then browse the Excel Sheet to be uploaded
4. Clicking on Import option, you will find see a popup window opens. Select Range of Cells in the Range Type
5. This option allows you to select the table columns and rows in excel as shown below. After selecting, you will see the selected field’s column and row data being populated in the Select Range field in the popup window
6. Now click on Import in the popup window. Your SharePoint list will be created from the Excel Sheet and the values are also populated as shown below:
You may face some issues while importing the Excel Sheet to SharePoint and the solution is follows:
Error: “Method ‘Post’ of object ‘IOWSPostData’ failed” in SharePoint
 Ø  Open the Excel Sheet Add-In EXPTOOWS.XLA which will is available at the following location C:\Program Files\Microsoft Office\Office12\1033 by default.
 Ø  Press Alt+F11 to display the Visual Basic code editor. Locate the form named "PublishForm" under the folder "Forms" and open the code view of this form.
 Ø  Search (Ctrl+F) for the line "lVer = Application.SharePointVersion(URL)" and place a new line "lVer=2" after that line.
 Ø  Now try to import the Excel Sheet to the SharePoint, you will not get any errors.

Error: "The specified file is not a valid spreadsheet or contains no data to import"

This error occurs when the site you are opening do not have permission to file system. To give permission follow the below steps:
In Internet Explorer click Tools --> Internet Options -> Click Security tab --> Select Trusted Sites --> In the Sites add your URL here.
Now you will be able to import the file properly.
Hope this post helps you! Please free to comment and share this post.

1 comment:

  1. importing works no problem. But now I want to also change the list structure from multi line text to selection. How do I go about doing that without having to change each individual entry?


Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.