By the way, Excel can import json files very nicely as a data source, the same way as it can generate live table views from various other databases (see step-by-step instructions below). So, Excel’s limitation is not that it cannot load JSON files, but in what analysis it can do on them and that you need to use the GUI or VBA (or maybe C#), which are not that well suited for data analysis.
Step-by-step instructions for importing a markups json file (*.mrk.json) into Excel as a live data source:
- In the ribbon, choose Data / Get data / From file / From JSON, and select the *.mrk.json file
= Table.SplitColumn(#"Extracted Values1", "Column1.position", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.position.1", "Column1.position.2", "Column1.position.3"})
- Click on “List” in “Markups” row
- Click on “Record”
- Click on “List” in “controlPoints” row
- Click “To Table”, click OK
- Click on the field selector and choose the fields you want in the table, for example “label”, “position” and “positionStatus”
- Split position column by clicking on the field selector and then choosing “Extract values…”
- Choose semicolon as delimiter and click OK
- Click “Split column” / “By delimiter”, make sure semicolon is used as delimiter and then click OK
- Click Close&Load
You only need to do this once, from this point the markups json file acts as a database (you can update table content from the json file with a single click or enable auto-refresh, etc.), you can change the input json file, etc.