How To Open Json File In Excel 2016

How to convert gstr2a json file into excel file for gst audit reporting through power query.

How to open json file in excel 2016. The get transform experience was not available in excel 2007 so you can use an office data connection odc file to connect to a microsoft sql server database from an excel 2007 workbook. In this post we will learn how to import json to excel. We will use odbc powerpack to connect and query a json file. Sql server is a full featured relational database program that is designed for enterprise wide data solutions that require optimum performance availability scalability and security.

Find the file on your disk and click import. This should bring you into the query editor. Missing json option at data new query from file there is allegedly an option to import json data in xl 2016 but the option is missing in my copy. If you are using a web api you may choose instead to import the data directly from the web.

When clicking from json you will be presented with a file browser. It isnt there in customize the ribbon so it cant be added from there either. Using get transform formerly powerquery allows you to write a query to create a table from your json data. Open the data in the query editor.

The easiest way is to use the file browser and copy the path to the file and then add the file name at the end. This article also covers creating excel from soap xml web service so read full article to learn about soap api export. Json stands for java script object notation and it is an open and standard format to read object with attributes and values. Connecting excel 2016 with json data is easy.