How to export user information to Excel

SharePoint provides you “Export to Spreadsheet” feature across almost all list, allowing you to export your list content to Excel. But not all lists have such functionality. For example, this option is not available for User list.

Here are the details:

We can achieve this by using two methods:

1. OWSSVR Functionality

2. Open with access

OWSSVR Functionality:

This export functionality is just using owssvr.dll, so you can use the following URL to open your users in Excel

http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=[LISTID]&View=[VIEWID]&CacheControl=1 

Everything you need is getting the right ID of the list and its view. To get these values follow the next steps:

·         Go to the “People and Groups” page of your site

·         Navigate  to “Settings->List Settings” options via Toolbar

·         Click on “List View” in “View” section to edit the List View.

·         Choose the fields you want to export (need to click OK and open view for edit again)

·         Copy the “List” and “View” IDs from URL in this the page, where you edit List View.

·         Save copy-pasted ID and replace [LISTID] and [VIEWID] in our URL.

·         Open original URL http://[site]/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqyin browser, with the right values

Open with access method: this is the most simple method to extract the data.

·         Open any custom list by using the option “open with access”

·         When the list will open in access then you will see the option of “user information list” in the left hand side.

·         Copy and paste it in excel and that’s it. You are done.

Question sequence gets disordered when export a survey with Rating Scale questions to Excel sheet.

Issue:
Question sequence is disordered when we export a survey list to excel
Resolution – Only to get questions in proper sequence in Excel sheet!!

• Checked Survey settings
• Found there were total 50 questions and found almost 30 questions were created using "Rating Scale" category
• Changed question ordering from Survey settings > Change order of questions
• Previously it was from Q-1 > Q-1-a….Q-5 > Q-5-e
• Changed it to reverse order and arranged Q-5-e > Q-5…..Q-1-a > Q-1
• Exported to spreadsheet, and got all the questions in right order.
• Now another issue occurred, Q-6 (Multiple Line of Text) came first in datasheet, so from view removed it to be displayed or to be exported to spreadsheet.
• The other issue noticed was when user clicks on "Respond to this survey", user will get the same reverse orders of questions (i.e. Q-5, 4, 3, 2, 1, 6)
• Though this helps us to get questions in proper sequence only in Spreadsheet but this cannot be implemented as this occurs disordering of questions in survey.

Found 2 alternate workarounds
Workaround 1

• Changed all "Rating Scale" questions to "Choice" category with "Radio button" & fill in "mandatory" option
  
• Order remained same (i.e. Q- 1, 2, 3, 4, 5, 6)
• Now it worked proper for both scenarios, user got the right order when open "Respond to this survey" and also got the right order when exported to excel.

Workaround 2

• Created a page and added a Data View Web part using SPD on the page
• Used SharePoint survey as a Data source and added all questions / columns in to the web part
• Then from Data view web part exported data to excel

Feel free to reach me in case of any queries on above steps. Thanks

Lookup column gives "#" symbol when export to spreadsheet

Issue:

There are 3 lookup columns, when I export this list to spreadsheet these lookup fields export with #;ID attached to them, and I got "testdata#;15"  instead of "testdata" I get

Resolution:

It was resolved after unmarking check-box for "Allow Multiple Values" for that lookup column under column settings

Import / Export data from a SharePoint page (which is not a List / Library) to Excel

To export a data from a page (not Document Library / List) to Excel

• Open MS Excel 2007
• Go to Data Tab
  
• Select From Web option

• Put in the URL with http://URL/_layouts/storman.aspx > Click GO

• It will display the Storage Space Allocation page in the pop up window
• Select the area which you want to import
• Click on import button
• It will import the data from Storage Space Allocation page to Excel

Please let me know in case of any queries.

Thank you.

Another success story with Export to Spreadsheet Error

Recently I came across very weird issue of “Export to Spreadsheet” in MOSS 2007 list.

Here I am using word "weird"  because I did not find this issue related to IE settings.

When I click on one of the list it says “Cannot connect to the server at this time. Changes to your data cannot be saved” and at the same time my collogues were able to get this working.

Troubleshot the problem from multiple aspects and narrowed down the problem to machines/repro scenario when the SharePoint site had been accessed from Vista/Win7 clients with SSL (https) and with the local user account.
Turned out that the problem was related to certificate revocation lists (CRLs) and had been an issue due to the following setting in IE: "Check for server certificate revocation" which is switched on by-default on Vista/Win7 clients as opposed to XP clients. When this IE switch got turned off, everything was working fine.