使用python脚本将JSON嵌套到CSV [英] nested JSON to CSV using python script

问题描述

我是python的新手，我有一个很大的json文件，我需要将其转换为csv-下面是一个示例

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

{状态":成功"，名称":"Theresa May"，位置":"87654321"，"AccountCategory":业务"，"AccountType":当前"，"TicketNo":"12345-12，" AvailableBal:" 12775.0400，" BookBa:" 123475.0400，" TotalCredit:" 1234567，" TotalDebit:" 0，" Usage:" 5，" Period:"2014年5月11日至2014年7月11日"，货币":"GBP"，申请人":天使"，签名者":[{名称":不可用"，"BVB":不可用"}]，详细信息":[{"PTransactionDate":"14年7月24日"，"PValueDate":"13年7月24日"，"PNarration":现金存款"，"PCredit":"0.0000"，"PDebit:" 40003.0000，" PB余额:" 40003.0000}，{" PTransactionDate:" 14-Jul-14，" PValueDate:" 23-Jul-14，" PTest:"现金存款，"PCredit:" 0.0000，" PDebit:" 40003.0000，" PBalance:" 40003.0000}，{" PTransactionDate:" 25-Jul-14，" PValueDate:" 22-Jul-14，"PTest:"现金存款，" PCredit:" 0.0000，" PDebit:" 40003.0000，" PBalance:" 40003.0000}，{" PTransactionDate:" 25-Jul-14，" PValueDate:"14年7月21日"，"PTest":现金存款"，"PCredit":"0.0000"，"PDebit":"40003.0000"，"PB余额":"40003.0000"}，{"PTransactionDate":"14-Jul-14"，"PValueDate":"20-Jul-14"，"PTest":现金存款"，"PCredit":"0.0000"，"PDebit":"40003.0000，" PBalance:" 40003.0000}]}

{ "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"}]}

我需要它显示为

名称，状态，位置，帐户类别，帐户类型，可用余额，总贷方，总借方等作为列，

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

pcredit，pdebit，pbalance，ptransactiondate，pvaluedate和'ptest'每行都有新值，如JSON文件所示

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

我已经设法将此脚本放到网上，但最后却显示了一个空的csv文件.我做错了什么?我已经使用了在线json到csv转换器，并且可以使用，但是由于这些都是敏感文件，我希望使用自己的脚本进行编写/管理，以便我可以确切地了解它的工作方式.请参阅以下有关我的python脚本的信息-我可以对更改内容提出建议吗?谢谢

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

推荐答案

这适用于您发布的JSON示例.问题是您嵌套了 dict ，并且无法根据需要创建 pcredit，pdebit，pbalance，ptransactiondate，pvaluedate和ptest 的子标题和子行.

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.

您可以使用 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)

这篇关于使用python脚本将JSON嵌套到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！