nested JSON to CSV using python script

Question

i'm new to python and I've got a large json file that I need to convert to csv - below is a sample

{ "status": "success","Name": "Theresa May","Location": "87654321","AccountCategory": "Business","AccountType": "Current","TicketNo": "12345-12","AvailableBal": "12775.0400","BookBa": "123475.0400","TotalCredit": "1234567","TotalDebit": "0","Usage": "5","Period": "May 11 2014 to Jul 11 2014","Currency": "GBP","Applicants": "Angel","Signatories": [{"Name": "Not Available","BVB":"Not Available"}],"Details": [{"PTransactionDate":"24-Jul-14","PValueDate":"24-Jul-13","PNarration":"Cash Deposit","PCredit":"0.0000","PDebit":"40003.0000","PBalance":"40003.0000"},{"PTransactionDate":"24-Jul-14","PValueDate":"23-Jul-14","PTest":"Cash Deposit","PCredit":"0.0000","PDebit":"40003.0000","PBalance":"40003.0000"},{"PTransactionDate":"25-Jul-14","PValueDate":"22-Jul-14","PTest":"Cash Deposit","PCredit":"0.0000","PDebit":"40003.0000","PBalance":"40003.0000"},{"PTransactionDate":"25-Jul-14","PValueDate":"21-Jul-14","PTest":"Cash Deposit","PCredit":"0.0000","PDebit":"40003.0000","PBalance":"40003.0000"},{"PTransactionDate":"25-Jul-14","PValueDate":"20-Jul-14","PTest":"Cash Deposit","PCredit":"0.0000","PDebit":"40003.0000","PBalance":"40003.0000"}]}

I need this to show up as

name, status, location, accountcategory, accounttype, availablebal, totalcredit, totaldebit, etc as columns,

with the pcredit, pdebit, pbalance, ptransactiondate, pvaluedate and 'ptest' having new values each row as the JSON file shows

I've managed to put this script below together looking online, but it's showing me an empty csv file at the end. What have I done wrong? I have used the online json to csv converters and it works, however as these are sensitive files I'm hoping to write/manage with my own script so I can see exactly how it works. Please see below for my python script - can I have some advise on what to change? thanks

import csv
import json


infile = open("BankStatementJSON1.json","r")
outfile = open("testing.csv","w")

writer = csv.writer(outfile)

for row in json.loads(infile.read()):
    writer.writerow(row)

    import csv, json, sys

    # if you are not using utf-8 files, remove the next line
    sys.setdefaultencoding("UTF-8")  # set the encode to utf8
    # check if you pass the input file and output file
    if sys.argv[1] is not None and sys.argv[2] is not None:
        fileInput = sys.argv[1]
        fileOutput = sys.argv[2]
        inputFile = open("BankStatementJSON1.json","r")  # open json file
        outputFile = open("testing2.csv","w")  # load csv file
        data = json.load("BankStatementJSON1.json")  # load json content
        inputFile.close()  # close the input file
        output = csv.writer("testing.csv")  # create a csv.write
        output.writerow(data[0].keys())  # header row
        for row in data:
            output.writerow(row.values())  # values row

The JSON file doesn't have rows, it has keys and values. – epinal Mar 20 '18 at 19:49 — epinal, Mar 20 '18 at 19:49

score 0 · Answer 1 · answered Mar 20 '18 at 19:29

0

Make sure you're closing the output file at the end as well.

answered Mar 20 '18 at 19:29

Nico Mitchell

111
1
6

epinal · Accepted Answer · 2018-03-20T20:13:58.957

0

This works for the JSON example you posted. The issue is that you have nested dict and you can't create sub-headers and sub rows for pcredit, pdebit, pbalance, ptransactiondate, pvaluedate and ptest as you want.

You can use csv.DictWriter:

import csv
import json

with open("BankStatementJSON1.json", "r") as inputFile:  # open json file
    data = json.loads(inputFile.read())  # load json content

with open("testing.csv", "w") as outputFile:  # open csv file
    output = csv.DictWriter(outputFile, data.keys())  # create a writer
    output.writeheader()
    output.writerow(data)

edited Mar 20 '18 at 20:13

answered Mar 20 '18 at 20:06

epinal

1,415
1
13
27

Hi, This seems to work in terms of outputting a CSV file - the csv file has the headers I needed, and also outputs the status, name, etc. However the rest of the json content from ptransactiondate until the end is actually inside the single column 'Details' - how would I get the rest of it (such as pcredit, pdebit, ptransaction, etc' to have their own columns also? Thanks for your help. – SS360 Mar 21 '18 at 09:44
That's the issue 'Details' has a nested dict with its own headers. Check this: https://stackoverflow.com/questions/29400631/python-writing-nested-dictionary-to-csv and – epinal Mar 21 '18 at 14:47

nested JSON to CSV using python script

2 Answers2

Linked