Website XXL

How to use Data Explorer to query a SharePoint 2013 List?

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.

1_Export2Excel

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.

2_EnableConnections

After allowing Excel to connect to SharePoint, all the data from list is downloaded into a spreadsheet.

3_ExcelList

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.

4_SharePointList

Next, we must specify the URL to our SharePoint site.

5_SP_URL

One important step is about the authentication to the SharePoint site.
Here we can connect:

  • Anonymous;
  • Using Windows credentials;
  • Using MOS ID.

6_SignIn

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.

7_MOS_ID

In the bellow image we can see that the data is downloaded (101 KB when I took the screenshot).

8_MOS_SignIn_Another

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;
  • etc.

9_Query_Editor

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.

10_Query_Settings

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”.

11_PowerPivot

Account Information

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.

12_DataSettings

If we click on Edit Credential, for the selected site all we can do is to sign out and sign in with another account.

13_EditCredentials

In order to delete an site and it’s stored credentials, click on the desired site and then click on the Delete option.

14_DeleteData

Now, we have to confirm the deletion, by clicking on the Delete option.

15_DeleteConfirmation

Now, the selected site was deleted along with the credentials.

16_NoData

Leave a Reply

Continuănd navigarea, accepți folosirea cookie-urilor. detalii

Setările cookie de pe acest site sunt setate pentru a "permite cookie-uri", a personaliza conținutul si anunțurile, a asigura funcții de social media si analiza trafic, asta pentru a vă oferi cea mai bună experiență posibilă la navigare. Împărtășim de asemenea, informații cu privire la utilizarea site-ului nostru cu partenerii noștri social media, publicitate și de analiză. Dacă veți continua să utilizați acest site, fără a schimba setările de cookie sau fara a face clic pe butonul "De acord" de mai jos, atunci vă exprimați acordul pentru aceasta.

Inchide