Connecting to OData Feeds with Excel 2013

OData feeds, is connecting to data sources that supports Open Data Protocol, which is a data access protocol initially defined by Microsoft, it is similar to JDBC and ODBC although OData is not limited to SQL databases, the protocol was designed to provide standard CRUD access to a data source via a website.

 OData is the protocol is the data API for Microsoft Azure. Also one of the most popular OData client implementations are Microsoft SharePoint 2010, 2013 and WCF Data Services.

 There are two main ways to connect to OData feeds with Excel, which I'm going to address in this post. 
Say we are to connect to a SharePoint list called resources:

Connecting with Power Query

Open Excel and under POWER QUERY ribbon tab, click From Other Sources > From OData Feed.



In the OData Feed dialog box, enter the URL for an OData feed then click OK. 
Note: When connecting to SharePoint lists, usually your URL would be in the following format http://{WebApplication}/{Site}/_vti_bin/ListData.svc




If the OData feed requires user credentials, in the Access an OData feed dialog box:
  • Click Windows if the OData feed requires Windows Authentication.
  • Click Basic if the OData feed requires your username and password.
  • Click Marketplace key if the OData feed requires a Marketplace account key. You can click the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.
  • Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.
Then click Save.


Now, after we have connected to our OData source with SharePoint, we can work with the lists using the Navigator pane.


Connecting with Excel DATA


Under DATA ribbon tab, click From Other Sources > From OData Data Feed.



In the Data Connection Wizard dialog box, enter the URL for an OData feed. In case your current user is authenticated against the data feed source keep "Use the sign-in information for the person opening this file" selected, otherwise select "Use this name and password" and then provide authenticated user name and password. Click next


Now, we select the lists we wish to connect to.


At this step, Excel will create a connection file for us against the data source (OData Feed) we have supplied. Fill in File Name, Description and Friendly Name for our connection file, or we can leave those fields as they are.
Note: Checking the "Save password in file" will save the data access password in the connection file. We have to be aware that passwords are saved in clear text in such files


Finally, we decide what do we want to do with the imported data.

Comments

  1. Vicky RamJanuary 17, 2019 at 10:37 PM

    Thank you sharing this kind of noteworthy information. Nice Post.

    businessexceltemplates
    Education

    ReplyDelete
    Replies
    1. Vale Co XeniaJuly 19, 2024 at 5:57 AM

      OData (Open Data Protocol) is a standard protocol for querying and updating data. Excel provides robust capabilities to connect and interact with OData feeds, making it a powerful tool for data analysis and reporting.

      Big Data Projects For Final Year Students


      Steps to Connect to an OData Feed in Excel
      Obtain the OData Feed URL: This is the endpoint provided by the data source you want to connect to.
      Open Excel: Launch Microsoft Excel.
      Access the Get Data Menu: Navigate to the "Data" tab and click on "Get Data."
      Choose OData Feed: Select "From Other Sources" and then "From OData Feed."
      Enter the OData Feed URL: Paste the obtained OData feed URL into the provided field.
      Authentication: If required, provide authentication credentials for accessing the data.
      Select Tables or Views: Choose the specific data entities you want to import.
      Load Data: Select the desired load options (e.g., PivotTable Report, PivotChart, or existing worksheet).

      Machine Learning Final Year Projects

      Deep Learning Projects for Final Year Students

      Delete

Post a Comment

Popular posts from this blog

Machine Learning Explained!

Image
The minute we hear the word machine learning, many thoughts rush into our minds, starting from high-tech robotics, sophisticated complex algorithms and ending by Sci-fi movies (of course!). Well, we might be partially right if the plan is to assemble a robot or reinvent an auto-pilot car, but this is no longer the case these days! In the recent years, Machine Learning is being widely used in business scenarios or – to be fair – it’s even becoming mainstream! Predictive machine learning modules and/or sentiments are being used in our day-to-day tools and utilities. For example, there are off-the-shelf SaaS sentiments to tell whether a tweet from a customer is a good feedback (happy customer) or a bad feedback (unhappy customer). Another example that we could find off-the-shelf is face recognition, which is a service we could feed with a couple of (face) photos of a person, and then feed a totally different photo – of the same person – other than the ones provided at the begin...
Read more

Building JSON Objects in Microsoft Flow

Image
A couple of days ago, I was helping a friend with a case in Microsoft Flow ... So I thought this is a good time to write a new article that would be a reference for my friend, and hopefully others would find this useful if they came across the same issue. The Case It's very simple and straight forward, we have a flow that gets initiated once an item is added to a SharePoint list, and we want to return a JSON object of the newly added item ... Easy! Good to know... Now before going into the solution, it's good to know that Microsoft Flow itself handles data within as JSON, whereas Flow is not only a process automation service, it's an integration service as well. Therefore it needs a common ground/language/format - you name it - to handle all data types from all the different sources (SharePoint list, SQL Table, Excel, etc...) This is How... Well, there are two different ways to tackle the case above, and I'm going to list them both in this article....
Read more

Microsoft Flow as a Service for Custom Applications

Image
I've been so eager to write about Microsoft Flow since I have started using it about a year ago! However, I've seen a lot of articles out there by community contributors talking about almost every single capability that Flow can do ... In addition, I personally believe that the Microsoft Flow team is doing a good job putting people on the right learning track. There are many scenarios that we could consider using Flow with, however in this very post I'll be writing about when to consider Flow while building custom developed applications ... Flow could be your "LEGO"  building block for almost every application! As developers, almost every single one of us have developed against at least one API, and I surely believe that many have developed APIs that connects to third party services and/or social media ... Now, this task is usually a bit costly during the project, whereas developers have to go through the technical documentation for those APIs and absorb th...
Read more