Does LibreOffice Calc support JSON file importing/sorting?

Question

I've been trying to import a couple of .json files into LibreOffice Calc.

Although I can get the raw data in, it isn't sorting as I would think it might (by placing different pieces of info into each cell).

Does LibreOffice provide support for importing JSON files and sorting them out in cells? (In other words, import + sort)?

If there doesn't seem to be direct support for this, would converting to CSV be the next logical step in order to get the data into Calc?

Bott

As of November 2017, LibreCalc still doesn't support opening JSON files (while Excel does). There's a feature request for this. – Troutman

Answer 1

Had the same problem myself (that's how I found this question).

So, for the next person finding this - the answer is no - LibreOffice Calc does not support direct import of JSON.

And the next logical step indeed is converting to CSV. There are free online JSON to CSV converters, and using one of them (http://www.convertcsv.com/json-to-csv.htm), I was easily able to make a correct CSV which Calc imports without a problem.

One possible caveat is if you have complex objects represented in JSON - that may not be convertible to CSV, but then again, if it doesn't fit into CSV, it probably doesn't fit into spreadsheet format either.

Founder

Answer 2

There's a LibreOffice GetRest plugin with documentation written in broken English, that has a "parseJSON" formula. It won't convert JSON to CSV (without a lot of grunt work) but it might help your use case.

Crib

That GitHub seems to be abandoned and the .odt is no longer there. But it has been preserved here: gist.github.com/ThomasG77/4ed97370af8355feabf50cb2909198a0 I just re-installed it on my machine and it continues to function well with Calc 7.2.6.2. – Heilman

Answer 3

Not a direct answer to the question, but a simple workaround. Assuming your JSON is a flat array of data columns, it can be converted to CSV with this python snippet — requires python3 with the pandas module — pip install pandas _{(or use google colab which provides python + pandas ootb)}:

import sys
from pathlib import Path
import pandas as pd

input_path = Path(sys.argv[1])
output_path = input_path.with_suffix('.csv')

df = pd.read_json(input_path)
df.to_csv(output_path.open(mode='w'), encoding='utf-8', index=False, header=True)

This takes the path to the JSON file as the first argument and creates the CSV in the same folder.

Also see pandas.read_json() and DataFrame.to_csv().

Marchioness