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
– epinal

2018-03-20 19:49:27 +00:00
Commented Mar 20, 2018 at 19:49 — epinal
– epinal, Commented Mar 20, 2018 at 19:49

epinal · Accepted Answer · 2018-03-20 20:13:58Z

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, 2018 at 20:13

answered Mar 20, 2018 at 20:06

epinal

1,4951 gold badge14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SS360 Over a year ago

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.

epinal Over a year ago

That's the issue 'Details' has a nested dict with its own headers. Check this: stackoverflow.com/questions/29400631/… and

Nico Mitchell · Accepted Answer · 2018-03-20 19:29:29Z

0

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

answered Mar 20, 2018 at 19:29

Nico Mitchell

1111 silver badge6 bronze badges

Collectives™ on Stack Overflow

nested JSON to CSV using python script

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related