How to Convert JSON file into CSV using Python

Question

I am trying to fetch the execution details of one user, and all those data i am capturing in a JSON and saving it , i want to convert this from JSON and write all the data from JSON to a csv file, Delimiter = ' , '

import os
import re
import json
import warnings
import urllib.request
import csv

warnings.filterwarnings('ignore', message='Unverified HTTPS request')

url = "http://machine245.local:4450/api/35/project/ProjectName01/executions"
headers = {
  'Accept': 'application/json',
  'X-Rundeck-Auth-Token': 'kP8s90rpfsdjfsdkHNKSLndskdsksd'
}
response = requests.request("GET", url, headers=headers, verify = False)
#print(response.text.encode('utf8'))

response_value = response.json()
response_value = json.dumps(response_value)
resp = json.loads(response_value)

print(resp)

with open('execute.csv','w') as executeData:
    csvWriter = csv.writer(executeData,delimiter=',')
    count = 0
    for result in resp:
        if count ==0:
            print("No Data to Read") 
            count+=1
        else:
        csvWriter.writerow(result.values)

Data in JSON is

[
  {
    "href": "http://machine245.local:4440/api/35/job/e3bc6e45-9571-4d8b-bcf6-69274532eea06",
    "averageDuration": 15089,
    "id": "e3bc6e45-9571-4d8b-bcf6-6927610esds6",
    "scheduleEnabled": true,
    "scheduled": false,
    "enabled": true,
    "permalink": "http://machine245.local:4440/project/Project01/job/show/e3bc6e45-9571-4d8b-bcf6-6927610eea06",
    "group": null,
    "description": " This job is to monitor the health of No servers ",
    "project": "Project01",
    "name": "Server Health Monitoring"
  },
  {
    "href": "http://machine245.local:4440/api/35/job/e3bc6e45-9671-4d8b-bcf6-64374532eea06",
    "averageDuration": 15089,
    "id": "b56bc6e45-9571-4d8b-bcf6-6927610esds6",
    "scheduleEnabled": true,
    "scheduled": false,
    "enabled": true,
    "permalink": "http://machine245.local:4440/project/Project01/job/show/e3bc6e45-9443-4d8b-bcf6-6927610eea06",
    "group": null,
    "description": " This job is to monitor the health of Client servers ",
    "project": "Project01",
    "name": "Client Health Monitoring"
  }
]

Can anyone help what is wrong here, or any alternate approach will do as well

Thanks in advance

Please update your question with a description of what is wrong. — quamrana, Dec 14 '20 at 11:44
Also, surely: `response_value = response.json()` gets you your object back from the `response`? There seems to be no need to convert it to a json string and back again. — quamrana, Dec 14 '20 at 11:45

score 1 · Answer 1 · answered Dec 14 '20 at 11:47

It's simple.

    for result in resp:
        if count ==0:
            print("No Data to Read") 
            count+=1
        else:
        csvWriter.writerow(result.values)

It's unnecessary if.

    for result in resp:
        csvWriter.writerow(result.values)

score 0 · Answer 2 · answered Dec 14 '20 at 11:44

0

You can try with pandas, read the file with pandas.read_json and save it with .to_csv

answered Dec 14 '20 at 11:44

Karots96

57
6

score 0 · Answer 3 · answered Dec 14 '20 at 12:21

You are iterating through list and every list item is a dictionary . So instead of csvWriter.writerow(result.values)

you should

 csvWriter.writerow(result.keys())
 csvWriter.writerow(result.values())

you can write key , value pairs of dictionary as you wish in here. You can write "key1,key2" then next line "value1,value2". or create a header with keys before loop then write values in every line for each item in the list. See more examples on this link How do I write a Python dictionary to a csv file?

why do you skip the first item in the list and doing json to string and back to json again. You might want to check these again

response_value = response.json()
#<class 'list'>
response_value = json.dumps(response_value)
#<class 'str'>
resp = json.loads(response_value)
#<class 'list'>

Also, it might not be really good idea to share tokens and real urls in public domain

Thanks a lot for the Help, I was able to achieve the keyvalue in csv file, but the value part in CSV had another list inside it , delimited by ' } ', the key value was delimited by comma(,) which i have used , but the values are in list how can i create seperate columns for this — Bernard, Dec 15 '20 at 06:09
if your item is a list yourList = [] with open('yourNewFileName.csv', 'w', ) as myfile: wr = csv.writer(myfile, quoting=csv.QUOTE_ALL) for word in yourList: wr.writerow([word]) — Gonzales Gokhan, Dec 15 '20 at 09:21

How to Convert JSON file into CSV using Python

3 Answers3

Linked