Thursday 10 November 2011

How to import an Excel SpreadSheet in SharePoint and save it as a Custom List

SharePoint provides an out of the box functionality to import an Excel SpreadSheet and save it as a Custom List. One just need to follow these steps:
Step-1: Click on "Site Actions->View All Site Content"
          
Step-2: In the "All Site Content" page, click on the "Create" link.
           
Step-3: Click on the "Import SpreadSheet" link in the "Custom Lists" section which will open the "New List" page.
             
Step-4: In the "New List" page, enter the name and description of the list to be created and click the "Browse" button and locate the Excel file which is to be imported. After that click the "Import" button which will opens the "Import to Windows SharePoint Services List" dialogbox. 
            
Step-5: In the "Import to Windows SharePoint Services List" dialogbox, select the "Range of Cells" option in the "Range Type" dropdown list.
            
Step-6: Then in the "Import to Windows SharePoint Services List" dialogbox, click on this button in the "Select Range" dropdown list which will enable you to select the particular rows and columns of excel spreadsheet you want to import in the custom list. Please don't forget to select the row that contains name of columns because these will become the field names in the custom list.
       
Step-7: Now in the "Import to Windows SharePoint Services List" dialogbox, click on this button , which will open the full view of "Import to Windows SharePoint Services List" dialogbox with the updated "Select Range" as you have selected in the excel spreadsheet.
             
Step-8: Now click on the "Import" button in the "Import to Windows SharePoint Services List" dialogbox, which will finally import the selected rows and colums of the Excel SpreadSheet as a Custom List in your SharePoint website.
         
Some known problems in this workflow which I had faced: 
When you try to import Excel 2007 SpreadSheets, you might get the "Method 'Post' of object 'IOWSPostData' failed" error. To resolve this error follow these steps:
1) Open the Excel Add-In EXPTOOWS.XLA which will be available at C:\Program Files\Microsoft Office\Office12\1033 by default.
2) 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.
3) Search (Ctrl+F) for the line "lVer = Application.SharePointVersion(URL)" and place a new line "lVer=2" after that.
4) Now try to Import the Excel SpreadSheet, it will be working fine.
But there is one more problem in this workflow when you import Excel 2007 SpreadSheets. When you will be done with the above steps, the error "Method 'Post' of object 'IOWSPostData' failed" will not appear again, but you might get an another error "Cannot connect to the server at this time. Your table cannot be published".
To resolve this error you just need to check that you have created a top level site or not. If not then just create the top level site and it will start working. For example you site's address where you are importing the Excel SpreadSheet will be like this "http://servername/sites/sitename". Now check that the top level site means a site at url "http://servername" is created or not. If not then just create the top level site at "http://servername" and try again to import the Excel SpreadSheet which will be working fine.
This is not looking logical but this is the only way I had used to resolve this issue and I had also not found any solutions for this issue. If someone knows a better solution for this issue, please let me know.

No comments:

Post a Comment