Skip to Content
Author's profile photo Deepak Tewari

Download data from an OData Service into Excel Directly

Hi All.

This is my first blog post on SCN. I don’t know if the members already know of this functionality but I was quite fascinated by this so I decided to share it anyway 🙂 .

I found this while playing around with MS Excel. It reads the data of an OData service directly into an Excel file (basically the Get_EntitySet data). Just follow the steps below:

==> Open MS Excel and create a blank workbook.

==> Click on the Data tab at the top and select From Other Sources->From OData Data Feed

 

==> Specify the link to the OData service in the next pop-up. In this case I am using the publically availaible Northwind OData Service. You can also take up your own OData service by providing necessary authentication details in the same popup. Then click Next

==> The wizard will fetch all the Entity Sets contained in the OData service. Select the one which you want to download to Excel. I selected PersonDetails here.

==> Click Finish and you will be asked for how you want the data to be displayed in Excel.

==> Select table and click OK. Wait for some time while your data is downloaded.

The data from the OData service is now availaible in Excel 🙂

This functionality also works fine for HANA Exposed OData services (xsodata).

Hope you all like this post.

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      september 2017 printable calendar

      october 2017 printable calendar

      Attendance Sheet Template

       

      Author's profile photo Mike Doyle
      Mike Doyle

      Nice work for a first blog, Deepak. I didn't know you could do this. OData was originally a Microsoft idea, so that would explain why they added this feature to Excel

      Author's profile photo Deepak Tewari
      Deepak Tewari
      Blog Post Author

      Thanks a lot Mike  🙂

      Author's profile photo Martin Fischer
      Martin Fischer

      Cool that you mentioned this option.

      There is actually a very cool tool developed by SAP for even better integration including the possibility to edit and save data, including conflict resolution for concurrent changes. It also offers value helpers for the end user.

      The bad news: SAP stopped selling it. The reason behind: wrong licensing model. SAP expected that customers pay additional licensing fees for each user.

      The name of the tool: SAP Gateway for Microsoft. It's worth to take a look. Would still be a cool tool. SAP should just accept that customers will not pay extra money for it!

      Author's profile photo Deepak Tewari
      Deepak Tewari
      Blog Post Author

      Thanks Martin for sharing this information. 🙂

      I will surely have a look at SAP Gateway for Microsoft tool.

      Author's profile photo Kirupakaran Kannan
      Kirupakaran Kannan

      Hi Deepak -  To access the data from the ODATA service to excel directly - how does the authentication process works ? do we need an Database user   ? or an application user ID ?

       

      Thanks

      Kiru

       

      Author's profile photo Deepak Tewari
      Deepak Tewari
      Blog Post Author

      HI Kirupakaran,

      I used the procedure to get data from an SAP System. I used the same credentials that I use to log in to the system and it worked fine.

      So to your question, You can do it with an Application User ID.

      Regards,

      Deepak