Parse JSON data in Python to CSV file

Question

I need to parse the below Json data using python and write to a csv file. Below I have included only 2 server names but my list is big. Please help with sample code to get the desired output.

Below is my json data in a file server_info.json:

{
"dev-server":
    {
    "hoststatus":
        {
        "host_name":"dev-server",
        "current_state":"2",
        "last_time_up":"1482525184"
        },
    "servicestatus":
        {
        "/ Filesystem Check":
            {
            "host_name":"dev-server",
            "service_description":"/ Filesystem Check",
            "current_state":"1",
            "state_type":"1"
            },
        "/home Filesystem Check":
            {
            "host_name":"dev-server",
            "service_description":"/home Filesystem Check",
            "current_state":"2",
            "state_type":"2"
            }
        }
    },
"uat-server":
    {
    "hoststatus":
        {
        "host_name":"uat-server",
        "current_state":"0",
        "last_time_up":"1460000000"
        },
    "servicestatus":
        {
        "/ Filesystem Check":
            {
            "host_name":"uat-server",
            "service_description":"/ Filesystem Check",
            "current_state":"0",
            "state_type":"1"
            },
        "/home Filesystem Check":
            {
            "host_name":"uat-server",
            "service_description":"/home Filesystem Check",
            "current_state":"1",
            "state_type":"2"
            }
        }
    }
}

Expected Output:

output format:

hoststatus.host_name,hoststatus.current_state,hoststatus.last_time_up
-------------------------------------------------------------
dev-server,2,1482525184
uat-server,0,1460000000

and

output format:

servicestatus.host_name,servicestatus.service_description,servicestatus.current_state,servicestatus.state_type
--------------------------------------------------------------------------------
dev-server,/ Filesystem Check,1,1
dev-server,/home Filesystem Check,2,2
uat-server,/ Filesystem Check,0,1
uat-server,/home Filesystem Check,1,2

use json.loads() to get a python dict, then show us your attempts. this is a tedious code-writing task, better done by you. — Jean-François Fabre
– Jean-François Fabre ♦, Commented Dec 26, 2016 at 16:55
have you tried something or are you just asking us to do your work? — inetphantom
– inetphantom, Commented Dec 26, 2016 at 17:45
Thank you Jean. I did have the code, was looking for how to read the server names as keys. — Anil
– Anil, Commented Dec 26, 2016 at 17:58

kuriouscoder · Accepted Answer · 2016-12-26 17:22:29Z

1

Elaborating more on what Jean-Fracois Fabre mentioned, json.load() can be used to read a JSON file and parse into Python object representation of JSON. json.loads() does the same except that the input is a string instead of a file (see json module for more details).

Bearing this in mind, say if you have your server logs in a file then you can start with the following:

import json
file = open('logs.txt')
data = json.load(file) # now the JSON object is represented as Python dict
for key in data.keys(): # dev-server and uat-server are keys
    service_status = data[key]['servicestatus'] # this would give out the servicestatus
    host_status = data[key]['hoststatus'] # this would give out the hoststatus

With this, you could use csv module to write it as CSV file in the format you desire.

answered Dec 26, 2016 at 17:22

kuriouscoder

5,6428 gold badges30 silver badges41 bronze badges

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

Comments

Haruo Kinoshita · Accepted Answer · 2016-12-26 17:41:52Z

0

A example by list comprehension.

import json

d = json.loads(data)

print("\n".join([','.join((hstat['host_name'], hstat['current_state'], hstat['last_time_up']))
                 for g in d.values()
                 for k, hstat in g.items() if k == 'hoststatus']))

print("\n".join([','.join((v['host_name'], v['service_description'], v['current_state'], v['state_type']))
                 for g in d.values()
                 for k, sstat in g.items() if k == 'servicestatus'
                 for v in sstat.values()]))

answered Dec 26, 2016 at 17:41

Haruo Kinoshita

1151 silver badge3 bronze badges

2 Comments

Anil Over a year ago

this is giving me error: TypeError: expected string or buffer

Anil Over a year ago

the solution provided by kuriouscoder is working for me. Thanks for your help too.

Collectives™ on Stack Overflow

Parse JSON data in Python to CSV file

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related