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
– quamrana, Commented Dec 14, 2020 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
– quamrana, Commented Dec 14, 2020 at 11:45

Akihito KIRISAKI · Accepted Answer · 2020-12-14 11:47:49Z

1

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)

answered Dec 14, 2020 at 11:47

Akihito KIRISAKI

1,37310 silver badges13 bronze badges

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

Comments

Karots96 · Accepted Answer · 2020-12-14 11:44:27Z

0

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

answered Dec 14, 2020 at 11:44

Karots96

578 bronze badges

Comments

Gonzales Gokhan · Accepted Answer · 2020-12-14 12:21:44Z

0

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

answered Dec 14, 2020 at 12:21

Gonzales Gokhan

4924 silver badges13 bronze badges

2 Comments

Bernard Over a year ago

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

Gonzales Gokhan Over a year ago

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])

Collectives™ on Stack Overflow

How to Convert JSON file into CSV using Python

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related