Multiple jsons to csv
<p>I have multiple files, each containing multiple highly nested json <em>rows</em>. The two first rows of one such file look like:</p>

<pre><code>{
"u":"28",
"evv":{
       "w":{
            "1":400,
            "2":{
                 "i":[{
                       "l":14,
                       "c":"7",
                       "p":"4"
                       }
                     ]
                 }
           }
       }
}
{
"u":"29",
"evv":{
       "w":{
            "3":400,
            "2":{
                 "i":[{
                       "c":14,
                       "y":"7",
                       "z":"4"
                       }
                     ]
                 }
           }
       }
}
</code></pre>

<p>they are actually rows, I just wrote them here this way for more visibility.</p>

<p>My question is the following:</p>

<p><del>Is there any way to convert <em>all</em> these files to one (or multiple, i.e. one per file) csv/excel... ?</del></p>

<p>Is there any simple way, that doesn't require writing dozens, or hundreds of lines in Python, specific to my file, to convert <em>all</em> these files to one (or multiple, i.e. one per file) csv/excel... ? One example would be using an external library, script... that handles this particular task, regardless of the names of the fields.</p>

<p>The trap is that some elements do not appear in each line. For example, for the <em>"i"</em> key, we have 3 fields (l, c, p) in the first json, and 3 in the second one (c, y, z). Ideally, the csv should contain as many columns as possible fields (e.g. evv.w.2.i.l, evv.w.2.i.c, evv.w.2.i.p, evv.w.2.i.y, evv.w.2.i.z) at the risk of having (many) null values per csv row.</p>

<p>A possible csv output for this example would have the following columns:</p>

<pre><code>u, evv.w.1, evv.w.3, evv.w.2.i.l, evv.w.2.i.c, evv.w.2.i.p, evv.w.2.i.y, evv.w.2.i.z
</code></pre>

<p>Any idea/reference is welcome :)</p>

<p>Thanks</p>

<p>No, there is no general-purpose program that does precisely what you ask for. </p>

<p>You can, however, write a Python program that does it.</p>

<p>This program might do what you want. It does not have any code specific to your key names, but it is specific to your file format.</p>

<ul>
<li>It can take several files on the command line.</li>
<li>Each file is presumed to have one JSON object per line.</li>
<li>It flattens the JSON object, joining labels with "."</li>
</ul>

<p>&nbsp;</p>

<pre><code>import fileinput
import json
import csv


def flattify(d, key=()):
    if isinstance(d, list):
        result = {}
        for i in d:
            result.update(flattify(i, key))
        return result
    if isinstance(d, dict):
        result = {}
        for k, v in d.items():
            result.update(flattify(v, key + (k,)))
        return result
    return {key: d}

total = []
for line in fileinput.input():
    if(line.strip()):
        line = json.loads(line)
        line = flattify(line)
        line = {'.'.join(k): v for k, v in line.items()}
        total.append(line)

keys = set()
for d in total:
    keys.update(d)

with open('result.csv', 'w') as output_file:
    output_file = csv.DictWriter(output_file, sorted(keys))
    output_file.writeheader()
    output_file.writerows(total)
</code></pre>

python json excel csv