extract_ods_to_json.py

"""

takes an input ODS file
runs through each sheet taking the top row as the keys and building json data of the other rows

the result json is a list of all rows on all sheets in json format, saved as a file with the orginal filename appended .json


https://pypi.org/project/pyexcel-ods3/

pip install pyexcel-ods3


"""
from pyexcel_ods3 import get_data
import json
import sys
import os


filename = sys.argv[1]

print("attempt to convert filename: \"{}\"".format(filename))

assert(os.path.exists(filename))


# converts all the sheets of a ods into json using the convention that the first row is the keys
# the next rows if they have anything in them is the data
# the json is a giant list of entries
def convert_ods_to_json(filename, out_filename=None):

    data = get_data(filename)

    items = []

    print("load file: \"{}\"".format(filename))

    for sheet_name in data:

        print("found sheet: \"{}\"".format(sheet_name))

        sheet = data[sheet_name]

        keys = None

        for row in sheet:

            item = {}

            if not keys:  # first row as keys
                keys = row
            else:

                if len(row) > 0:
                    items.append(item)

                    for i in range(len(row)):
                        val = row[i]

                        key = keys[i]

                        item[key] = val

    if not out_filename:
        out_filename = "{}.json".format(filename)

    print("save file to: \"{}\"".format(out_filename))

    # Write pretty print JSON data to file
    with open(out_filename, "w") as write_file:
        json.dump(items, write_file, indent=4)


convert_ods_to_json(filename)