In my last post, I promised that we’d get to Postman and see how we can work with the queries.  

But I changed my mind and decided that before we get to that, I want to show you how to load data directly into Excel with the ODATA URL that we used before. 

Now keep in mind, the point of these posts is to show you how we can create truly reliable reporting databases. In my world, dumping things into Excel leads to a life of VLOOKUP and pain

But as a quick way to view ODATA output, loading data to Excel is helpful.

I’m going to start with the URL I got from the Web Services page in Business Central as described in my previous post. This is the ODATA API for the Chart of Accounts:

https://api.businesscentral.dynamics.com/v2.0/{UseYourDomainGUIDHere}/Production/ODataV4/Company(‘CRONUS%20USA%2C%20Inc.’)/Chart_of_Accounts

Open a new Excel workbook and choose Get Data from the Data Menu:

Then choose From Other Sources -> From OData Feed:

Paste the URL in the box and click OK:

If this is the first time you’re connecting to this source, Excel will ask you for authentication. In my case, I’m going to choose Basic and enter my user name and the Web Services Access Key.

Please note:  Excel will save these credentials for future use. They are saved in Excel – not in the individual sheet. (More on this in a minute.)

You then click Connect:

Next, a preview screen appears. 

Click Load:

And then you have your data:

Now, if you don’t want your credentials saved, you need to return to Data -> Get Data and select Data Source Settings:

Permissions for various connections appear. Highlight your line and click Clear Permissions:

Excel will ask you to confirm that you want to clear permissions. Click Delete:

You can then close the previous path.

In my next blog post in this series, we’ll look at Postman. (I promise.)

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult