JSON Object to CSV file

[Peter Otten]

Sahlusar wrote:

> On Wednesday, June 17, 2015 at 11:00:24 AM UTC-4, Saran A wrote:
>> I would like to have this JSON object written out to a CSV file so that
>> the keys are header fields (for each of the columns) and the values are
>> values that are associated with each header field. Is there a best
>> practice for working with this? Ideally I would like to recursively
>> iterate through the key value pairs. Thank you in advance. I am using
>> Python 3.4 on Windows. My editor is Sublime 2.
>> 
>> {
>> "CF": {
>> "A": "5",
>> "FEC": "1/1/0001 12:00:00 AM",
>> "TE": null,
>> "Locator": null,
>> "Message": "Transfer Fee",
>> "AT": null,
>> "FT": null,
>> "FR": "True",
>> "FY": null,
>> "FR": null,
>> "FG": "0",
>> "Comment": null,
>> "FUD": null,
>> "cID": null,
>> "GEO": null,
>> "ISO": null,
>> "TRID": null,
>> "XTY": "931083",
>> "ANM": null,
>> "NM": null
>> },
>> "CF": "Fee",
>> "ID": "2"
>> }
> 
> My apologies:
> 
> I originally parsed this from an XML file.
> 
> It really should be:
> 
> {
> "Fee": {
> "A": "5",
> "FEC": "1/1/0001 12:00:00 AM",
> "TE": null,
> "Locator": null,
> "Message": "Transfer Fee",
> "AT": null,
> "FT": null,
> "FR": "True",
> "FY": null,
> "FR": null,
> "FG": "0",
> "Comment": null,
> "FUD": null,
> "cID": null,
> "GEO": null,
> "ISO": null,
> "TRID": null,
> "XTY": "931083",
> "ANM": null,
> "NM": null
> },
> "CF": "Fee",
> "ID": "2"
> }
> 
> The value, "Fee" associated with the key, "CF" does should not be included
> as a column header.
> 
> The CSV file, when opened with an application such as MS Excel, should be
> as follows:
> 
> (Column Header)----> CF               A          FEC
> 
> (Field Value)---->   Fee              5        1/1/0001 12:00:00 AM
> 
> My apologies for an confusion.

Forget about Excel, what should the CSV look like when opened in a text 
editor?

Here's my guess:


$ cat input.json
{
"Fee": {
"A": "5",
"FEC": "1/1/0001 12:00:00 AM",
"TE": null,
"Locator": null,
"Message": "Transfer Fee",
"AT": null,
"FT": null,
"FR": "True",
"FY": null,
"FR": null,
"FG": "0",
"Comment": null,
"FUD": null,
"cID": null,
"GEO": null,
"ISO": null,
"TRID": null,
"XTY": "931083",
"ANM": null,
"NM": null
},
"CF": "Fee",
"ID": "2"
}
$ cat json2csv.py
import csv
import json
import sys

def hook(obj):
    return obj

def flatten(obj):
    for k, v in obj:
        if isinstance(v, list):
            yield from flatten(v)
        else:
            yield k, v

if __name__ == "__main__":
    with open("input.json") as f:
        data = json.load(f, object_pairs_hook=hook)

    pairs = list(flatten(data))

    writer = csv.writer(sys.stdout)
    writer.writerow([k for k, v in pairs])
    writer.writerow([v for k, v in pairs])
$ python3 json2csv.py 
A,FEC,TE,Locator,Message,AT,FT,FR,FY,FR,FG,Comment,FUD,cID,GEO,ISO,TRID,XTY,ANM,NM,CF,ID
5,1/1/0001 12:00:00 AM,,,Transfer Fee,,,True,,,0,,,,,,,931083,,,Fee,2

But do you really want duplicate column names?