convert JSON to CSV in python3

Question

I have a JSONObject which needs to be written in CSV files along with headers.

My error:

` File "/usr/local/Cellar/python/3.7.5/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/__init__.py", line 341, in loads
    raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not list`

My JSONObject(updatedRecord):

[
      "{\"name\": \"Anu Ghosh\", \"email\": \"[email protected]\", \"mobile_number\": \"99999999\", \"skills\": [\"Recruitment\", \"International\", \"Coding\", \"Wifi\", \"Programming\", \"Training\", \"Mobile\", \"Sourcing\", \"Email\", \"English\", \"Design\", \"Electronics\", \"System\", \"Strategy\", \"Content\", \"Scheduling\", \"Technical\", \"Hardware\", \"Lighting\", \"C\"], \"college_name\": null, \"degree\": [\"Bachelor of Technology in \\nElectronics and \\nTelecommunication(B.Tech)\"], \"designation\": [\"Internet of Things (IOT)\"], \"experience\": \"1\", \"company_names\": null, \"no_of_pages\": 2, \"total_experience\": 0, \"fullName\": \"Anu Ghosh\", \"emailId\": \"[email protected]\", \"phoneNumber\": \"23454654434\", \"currentLocation\": \"Mumbai\", \"closestCity\": \"Mumbai\", \"resumeLink\": \"/Users/abc/Downloads/aaa.pdf\", \"applicationJobCode\": \"DEF\", \"applicationJobTitle\": \"HR\"}",
      "{\"name\": \"Prakash\", \"email\": \"[email protected]\", \"mobile_number\": \"+99877678888\", \"skills\": [\"Scala\", \"Google drive\", \"Database\", \"Rest\", \"Communication\", \"Zookeeper\", \"Programming\", \"Sql\", \"Python\", \"Agile\", \"Javascript\", \"Mobile\", \"Mysql\", \"Json\", \"Compliance\", \"Hotels\", \"Fabric\", \"Architecture\", \"Api\", \"Requests\", \"Reporting\", \"Operations\", \"Pycharm\", \"Troubleshooting\", \"Html\", \"Ui\", \"Video\", \"C++\", \"English\", \"Gap analysis\", \"Java\", \"Cloud\", \"Design\", \"Windows\", \"Visual\", \"Click\", \"Oracle\", \"Jira\", \"Scrum\", \"System\", \"Hadoop\", \"Security\", \"Schedules\", \"Ibm\", \"Spark\", \"Analysis\", \"Linux\", \"Project planning\", \"Intranet\", \"Postgresql\", \"Css\", \"Process\", \"Software development life cycle\", \"Technical\", \"Js\", \"Aws\", \"Sdlc\", \"C\", \"Certification\", \"Test cases\"], \"college_name\": null, \"degree\": [\"M.S (IT)\"], \"designation\": null, \"experience\": \"4\", \"company_names\": [\"Oracle\"], \"no_of_pages\": 7, \"total_experience\": 0.0, \"fullName\": \" prakash\", \"emailId\": \"[email protected]\", \"phoneNumber\": \"99998898998\", \"currentLocation\": \"Mumbai\", \"closestCity\": \"Mumbai\", \"resumeLink\": \"/Users/abc/Downloads/v_cv.pdf\", \"applicationJobCode\": \"ABVC\", \"applicationJobTitle\": \"Developer\"}"
]

I have written this code block which does not work.

def writeToCSV(updatedDBRecord):
record = json.dumps(updatedDBRecord)
record = json.loads(record)
f = csv.writer(open('/Users/varunprakash/Documents/candidate.csv','w'))
f.writerow(['Name','EmailId','PhoneNumber','Experience','Skill','CurrentLocation','ClosestCity','ResumeLink','ApplicationJobCode','ApplicationJobTitle'])
for data in record:
    print(data)
    f.writerow(data['fullName'],data['emailId'],data['phoneNumber'],data['experience'],data['skill'],data['currentLocation'],data['closestCity'],data['resumeLink'],data['applicationJobCode'],data['applicationJobTitle'])
f.close()

Any ideas are appreciated.

The sample you show doesn't seem to agree with your code. Anyway, the error message suggests that you are trying to write e.g. the skills field as a single string; but it's not a string, it's a list of strings. Trying to write structured data into CSV is a common beginner problem - CSV doesn't have any nested structures, so you have to serialize the data somehow (which is ugly and brittle) or simply choose a better output format which is compatible with your data model. — tripleee
– tripleee, Commented Nov 18, 2019 at 16:43

Fakher Mokadem · Accepted Answer · 2019-11-18 17:00:22Z

You messed up the loads and dumps at first, but your logic is sound.

import csv
import json
from pprint import pprint

def writeToCSV(updatedDBRecord):
    for data in updatedDBRecord: 
        record = json.loads(data)
        with open(r'c:\tmp\tmp.csv','w') as fp:
            f = csv.writer(fp)
            f.writerow(['Name','EmailId','PhoneNumber','Experience','Skills','CurrentLocation','ClosestCity','ResumeLink','ApplicationJobCode','ApplicationJobTitle'])
            f.writerow([record['fullName'],record['emailId'],record['phoneNumber'],record['experience'],record['skills'],record['currentLocation'],record['closestCity'],record['resumeLink'],record['applicationJobCode'],record['applicationJobTitle']])

data = [
      "{\"name\": \"Anu Ghosh\", \"email\": \"[email protected]\", \"mobile_number\": \"99999999\", \"skills\": [\"Recruitment\", \"International\", \"Coding\", \"Wifi\", \"Programming\", \"Training\", \"Mobile\", \"Sourcing\", \"Email\", \"English\", \"Design\", \"Electronics\", \"System\", \"Strategy\", \"Content\", \"Scheduling\", \"Technical\", \"Hardware\", \"Lighting\", \"C\"], \"college_name\": null, \"degree\": [\"Bachelor of Technology in \\nElectronics and \\nTelecommunication(B.Tech)\"], \"designation\": [\"Internet of Things (IOT)\"], \"experience\": \"1\", \"company_names\": null, \"no_of_pages\": 2, \"total_experience\": 0, \"fullName\": \"Anu Ghosh\", \"emailId\": \"[email protected]\", \"phoneNumber\": \"23454654434\", \"currentLocation\": \"Mumbai\", \"closestCity\": \"Mumbai\", \"resumeLink\": \"/Users/abc/Downloads/aaa.pdf\", \"applicationJobCode\": \"DEF\", \"applicationJobTitle\": \"HR\"}",
      "{\"name\": \"Prakash\", \"email\": \"[email protected]\", \"mobile_number\": \"+99877678888\", \"skills\": [\"Scala\", \"Google drive\", \"Database\", \"Rest\", \"Communication\", \"Zookeeper\", \"Programming\", \"Sql\", \"Python\", \"Agile\", \"Javascript\", \"Mobile\", \"Mysql\", \"Json\", \"Compliance\", \"Hotels\", \"Fabric\", \"Architecture\", \"Api\", \"Requests\", \"Reporting\", \"Operations\", \"Pycharm\", \"Troubleshooting\", \"Html\", \"Ui\", \"Video\", \"C++\", \"English\", \"Gap analysis\", \"Java\", \"Cloud\", \"Design\", \"Windows\", \"Visual\", \"Click\", \"Oracle\", \"Jira\", \"Scrum\", \"System\", \"Hadoop\", \"Security\", \"Schedules\", \"Ibm\", \"Spark\", \"Analysis\", \"Linux\", \"Project planning\", \"Intranet\", \"Postgresql\", \"Css\", \"Process\", \"Software development life cycle\", \"Technical\", \"Js\", \"Aws\", \"Sdlc\", \"C\", \"Certification\", \"Test cases\"], \"college_name\": null, \"degree\": [\"M.S (IT)\"], \"designation\": null, \"experience\": \"4\", \"company_names\": [\"Oracle\"], \"no_of_pages\": 7, \"total_experience\": 0.0, \"fullName\": \" prakash\", \"emailId\": \"[email protected]\", \"phoneNumber\": \"99998898998\", \"currentLocation\": \"Mumbai\", \"closestCity\": \"Mumbai\", \"resumeLink\": \"/Users/abc/Downloads/v_cv.pdf\", \"applicationJobCode\": \"ABVC\", \"applicationJobTitle\": \"Developer\"}"
]

writeToCSV(data)

This works as expected.

Collectives™ on Stack Overflow

convert JSON to CSV in python3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related