Converting nested JSON content dump to XLS
Question: I have a JSON dump from a content management site which follows the format:

    
    
    [
        {
            id: "obj1",
            children: [...]
        },
    
        {
            id: "obj2",
            children: [...]
        }
    
    ]
    

There are 2-4 nesting levels.

What would be the best way to convert this to Microsoft Excel XLS so that
nested levels are handled somehow, for the Excel-able customer to play with
their data?

With this particular data, one way to do this would create a new sheet for
each top level folder (nesting level). All sheets would contain the same
column names picked from the JSON objects in that particular folder.

Are there any ready-made tools for importing JSON to Excel?

Preferably as a command-line tool and if scripting is required then in Python.


Answer: It depends on your data.

If the nesting happens without any kind of repetition then simplest option is
to replicate everything or leave blank spaces if your data is complete enough
to be able to assume a repetition where you find a blank.

This means the XLS as a CSV will look like this:

    
    
    Element1 Element1.1 Element1.1.1 ...
    Element1 Element1.1 Element1.1.2 ...
    Element2 Element2.1 Element2.1.1 ...
    

Where each element is a child of the one at its left. You can see a parent
repeats as many times as children it has multiplied by how many times does
each of the children appear.

You can also do a very simple table with two columns:

    
    
    **Parent**    **Child**
    Element1      Element1.1
    Element1.1    Element1.1.1  
    Element1.1    Element1.1.2
    Element2      Element2.1
    Element2.1    Element2.1.1
    ...
    

What an element is depends on your granularity. You can group pairs of
key=values as a string, you can group several fields into one and parse it
back with regular expressions, or you can separate everything and consider the
key as an element and the value as another one.

Finally, if there is some regularity then you can take a more interesting
approach, assume you have some repeating fieldnames, in that case you can take
any of the previous approaches but using the fieldnames to generate a matrix
instead of a list. The first example is trivial since it is obviously a tuple
list that does already have an implicit ordinal header, the second one is a
table and may look like a matrix already, but you can do this.

    
    
    **Parent**    **Child (default)** **Repeating key1** **Repeating key2**
    e1            e1.1
    e1.1          e1.1.1
    e.1.1.1                           something          
    e.1.1         e.1.1.2
    e.1.1.2                                               somethingelse
    

So basically in the end you have a sparse matrix.

There are very interesting ways to store matrixes with three dimensions using
several sheets on an XLS, but human-readability may drop with it. It boils
down to the data you are using, **there is no general solution**.

