How to use Data Explorer to query a SharePoint 2013 List?
If you want to extract all data from a SharePoint List to a Excel workbook, an easy solution is to use Export to Excel.
SharePoint allows you to export a Web Query that downloads that list data into an spreadsheet.
Export to Excel
From the LIST tab click on the Export to Excel button.
When we run the Web Query, Excel asks us for permissions, in order to connect to an external data source.
Here we must click on Enable.
After allowing Excel to connect to SharePoint, all the data from list is downloaded into a spreadsheet.
Data Explorer “Preview” for Excel 2010/2013
According to Microsoft:
“Data Explorer” provides an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Data Explorer also provides you with the ability to search for public data from sources such as Wikipedia.
Download Link | Data Explorer
From the DATA EXPLORER tab, click on From Other Sources and then choose From SharePoint List.
Next, we must specify the URL to our SharePoint site.
One important step is about the authentication to the SharePoint site.
Here we can connect:
- Using Windows credentials;
- Using MOS ID.
Because I have my Office 365 account connected to Office 2013, in the MOS ID section I just click on Sign In and I am automatically signed in.
In the bellow image we can see that the data is downloaded (101 KB when I took the screenshot).
In the Query Editor we can see the data from our SharePoint List.
Here we can:
- apply a filer;
- sort the data;
- hide the formula bar;
To download the data from the SharePoint List, click on the Done button.
Now, in Excel we have the SharePoint data from the list.
On the right side we can see a settings pane.
In this pane we can:
- a – see the last update time and we can force a new data refresh;
- b – enable or disable the data downloading into the spreadsheet;
- c – load the data into PowerPivot.
Using the Load to Data Model option, the downloaded data is sent to PowerPivot and from there we can do some more advanced “stuff”.
At one of the above steps, Excel asked us to connect to the SharePoint site.
The DATA EXPLORER offers us an option that allows to change the account or to delete the stored connection data.
From the DATA EXPLORER tab click on Data Source Settings.
If we click on Edit Credential, for the selected site all we can do is to sign out and sign in with another account.
In order to delete an site and it’s stored credentials, click on the desired site and then click on the Delete option.
Now, we have to confirm the deletion, by clicking on the Delete option.
Now, the selected site was deleted along with the credentials.