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>Please check if this (python3) solution works for you. </p>

<pre><code>import json
import csv

with open('test.json') as data_file:
    with open('output.csv', 'w', newline='') as fp:
        for line in data_file:
            data = json.loads(line)
            output = [[data['u'], data['evv']['w'].get('1'), data['evv']['w'].get('3'),
                       data['evv']['w'].get('2')['i'][0].get('l'), data['evv']['w'].get('2')['i'][0].get('c'),
                       data['evv']['w'].get('2')['i'][0].get('p'), data['evv']['w'].get('2')['i'][0].get('y'),
                       data['evv']['w'].get('2')['i'][0].get('z')]]
            a = csv.writer(fp, delimiter=',')
            a.writerows(output)
</code></pre>

<p><strong>test.json</strong></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><strong>output</strong></p>

<pre><code>python3 pyprog.py 
dac@dac-Latitude-E7450 ~/P/pyprog&gt; more output.csv 
28,400,,14,7,4,,
29,,400,,14,,7,4
</code></pre>

python json excel csv