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

Post a Comment

Popular posts from this blog

Counting Down to ESPC21 Online

Image
This May 28 th , I will be delivering a full day tutorial with Serge Luca on Building Solutions with Power Apps & Power Automate . But that’s not all! On June 2nd, I will be delivering a session: Business RPA with Power Automate . Learn about what is RPA, its business benefits and how you can fit Power Automate Desktop to transform business processes in your organization, freeing your from repetitive tasks.   I will be taking any questions you may have live during my session, so come prepared!      ESPC21 is always amazing, whether in-person or virtual – the best speakers, topics and audience and everyone is so engaged, so I am really excited to be speaking at this event.  Register today to be sure you don’t miss it!   As a speaker at  ESPC21 Online  I can share with you a special 25% discount on Pro Access tickets, which includes a pre-event tutorial of your choice, all event sessions on demand and more. If interested, just use code ESPCPRO when booking  here .     You c
Read more

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