CODEWITHSUNDEEP
pythonjsoncsv
Oleg
Oleg

Reputation: 13

Build CSV with headers plus data out of JSON object

I got the following problem with Python.

given the following JSON object - I would like to

CSV headers

firstName,lastName,managersEmail,contractStartsDate

CSV contents

firstName,lastName,managersEmail,contractStartsDate
nameOfPerson,lastNameofPerson,someManager,2000-01-01
nameOfPerson2,lastNameofPerson2,someManager2,2000-02-02

my targetJSON.json

data = '{"details":[
{"firstName":"nameOfPerson,"lastName":"lastNameofPerson","managersEmail":"someEmail","managersName":"someManager",
    "departmentName":"someDepartment",
    "position":"somePosition",
    "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01",
    "company":"someCompany",
    "division":"someDivision",
    "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2",
    "position":"somePosition2",
    "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02",
    "company":"someCompany",
    "division":"someDivision2",
    "preferredName":"Unknown"}
]}'

My code looks like this


with open('targetJSON.json', 'r') as f:
    distros_dict = json.load(f)

for distro in distros_dict:
    print(distro['managersEmail'])


data_file = open("targetJSON.json", "r")
values = json.load(data_file)
data_file.close()

with open("usersData.csv", "wb") as f:
    wr = csv.writer(f)
    for data in values:
        value = data["managersEmail"]
        value = data["firstName"]
        for key, value in data.iteritems():
            #wr.writerow([key, value])
            wr.writerow([key.encode("utf-8"), value.encode("utf-8")])

But the results is complete gibberish, the CSV contains everything mixed up :-(

Upvotes: 0

Views: 1527

Answers (1)

Martin Evans
Martin Evans

Reputation: 46759

You need to use newline="" when using a csv.writer() with Python 3.x, wb is used for Python 2.x versions.

Using the sample JSON you've given, you would just need to iterate over the header fields and create a row from each entry in details. For example:

import json
import csv

data = """{"details":[{"firstName":"nameOfPerson","lastName":"lastNameofPerson","managersEmail":"someEmail",
    "managersName":"someManager", "departmentName":"someDepartment", "position":"somePosition", "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01", "company":"someCompany", "division":"someDivision", "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2", "position":"somePosition2", "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02", "company":"someCompany", "division":"someDivision2", "preferredName":"Unknown"}
]}"""

json_data = json.loads(data)
header = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(header)

    for entry in json_data["details"]:
        csv_output.writerow([entry[key] for key in header])

Giving you:

firstName,lastName,managersEmail,contractStartsDate
nameOfPerson,lastNameofPerson,someEmail,2000-01-01
nameOfPerson2,lastNameofPerson2,someEmail2,2000-02-02

If your JSON data contains duplicate entries, then you would have to first load all of the data and remove duplicates before starting to write the rows.

Alternatively, you could use a csv.DictWriter as follows:

import json
import csv

data = """{"details":[{"firstName":"nameOfPerson","lastName":"lastNameofPerson","managersEmail":"someEmail",
    "managersName":"someManager", "departmentName":"someDepartment", "position":"somePosition", "contractStartsDate":"2000-01-01",
    "contractEndDate":"2000-01-01", "company":"someCompany", "division":"someDivision", "preferredName":"Unknown"},
{"firstName":"nameOfPerson2","lastName":"lastNameofPerson2","managersEmail":"someEmail2","managersName":"someManager2",
    "departmentName":"someDepartment2", "position":"somePosition2", "contractStartsDate":"2000-02-02",
    "contractEndDate":"2000-02-02", "company":"someCompany", "division":"someDivision2", "preferredName":"Unknown"}
]}"""

json_data = json.loads(data)
fieldnames = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=fieldnames, extrasaction="ignore")
    csv_output.writeheader()
    csv_output.writerows(json_data["details"])

To read the data from an input JSON file, you can do the following:

import json
import csv

with open("sourceJSON.json", encoding="utf-8") as f_input:
    json_data = json.load(f_input)

fieldnames = ["firstName", "lastName", "managersEmail", "contractStartsDate"]

with open("usersData.csv", "w", newline="", encoding="utf-8") as f_output:
    csv_output = csv.DictWriter(f_output, fieldnames=fieldnames, extrasaction="ignore")
    csv_output.writeheader()
    csv_output.writerows(json_data["details"])

If you need to remove identical rows, then replace the last line with: 

csv_output.writerows(dict(t) for t in {tuple(entry.items()) : '' for entry in json_data["details"]})

Upvotes: 2

Related Questions