Have seen a lot of thread but unable to found the solution for mine. I want to convert one nested JSON to CSV in Python 2.7. The sample JSON file is as below:
sample.json # My JSON file that mainly contains a firewall rule"rulebase": [{"from": 1, "name": "test-policy", "rulebase": [{"action": "6c488338-8eec-4103-ad21-cd461ac2c473", "action-settings": {}, "comments": "FYI", "content": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "content-direction": "any", "content-negate": false, "custom-fields": {"field-1": "", "field-2": "", "field-3": ""}, "destination": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "destination-negate": false, "domain": {"domain-type": "domain", "name": "SMC User", "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"}, "enabled": true, "hits": {"first-date": {"iso-8601": "2016-09-04T22:21-0500", "posix": 1473045718000}, "last-date": {"iso-8601": "2018-03-19T03:37-0500", "posix": 1521448660000}, "level": "low", "percentage": "0%", "value": 36737474}, "install-on": ["6c488338-8eec-4103-ad21-cd461ac2c476"], "meta-info": {"creation-time": {"iso-8601": "2016-09-15T12:42-0500", "posix": 1473961370382}, "creator": "System", "last-modifier": "admin", "last-modify-time": {"iso-8601": "2018-08-30T18:36-0500", "posix": 1535672186192}, "lock": "unlocked", "validation-state": "ok"}, "rule-number": 1, "service": ["ef245528-9a3d-11d6-9eaa-3e5a6fdd6a6a", "dff4f7ba-9a3d-11d6-91c1-3e5a6fdd5151", "24bee257-6b37-49bb-99aa-557d993a0e48", "97aeb45c-9aea-11d5-bd16-0090272ccb30", "97aeb471-9aea-11d5-bd16-0090272ccb30"], "service-negate": false, "source": ["697bb7e0-0dfe-4070-a21a-68858daae98c", "349fb05c-99b2-4fb2-aea6-7b447d0e661c"], "source-negate": true, "time": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "track": {"accounting": false, "alert": "none", "per-connection": true, "per-session": false, "type": "598ead32-aa42-4615-90ed-f51a5928d41d"}, "type": "access-rule", "uid": "2da21174-0af8-4b5b-b02e-2957a24d70e1", "vpn": ["97aeb369-9aea-11d5-bd16-0090272ccb30"]}, {"action": "6c488338-8eec-4103-ad21-cd461ac2c472", "action-settings": {"enable-identity-captive-portal": false}, "comments": "", "content": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "content-direction": "any", "content-negate": false, "custom-fields": {"field-1": "", "field-2": "", "field-3": ""}, "destination": ["b17d4573-ad1a-4126-ae6d-c874ea919cda", "5b78417c-64ed-4566-9c76-e4e1af25a9ae", "acb8d280-2ec4-46b1-be9f-c676fa255fb5"], "destination-negate": false, "domain": {"domain-type": "domain", "name": "SMC User", "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"}, "enabled": true, "hits": {"level": "zero", "percentage": "0%", "value": 0}, "install-on": ["6c488338-8eec-4103-ad21-cd461ac2c476"], "meta-info": {"creation-time": {"iso-8601": "2018-07-25T16:27-0500", "posix": 1532554044090}, "creator": "admin", "last-modifier": "admin", "last-modify-time": {"iso-8601": "2018-08-31T16:00-0500", "posix": 1535749228997}, "lock": "unlocked", "validation-state": "ok"}, "name": "[email protected]", "rule-number": 2, "service": ["18ec9eaa-1657-4240-ab97-5f234623336b"], "service-negate": false, "source": ["293ef5ba-5235-464e-9247-bda26229a998", "b503873f-0c5f-4798-b87a-dd6ed4561b40"], "source-negate": false, "time": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "track": {"accounting": false, "alert": "none", "per-connection": true, "per-session": false, "type": "598ead32-aa42-4615-90ed-f51a5928d41d"}, "type": "access-rule", "uid": "fcc5a2c8-3a78-4cc5-9fd3-e7bd59eb36ba", "vpn": ["97aeb369-9aea-11d5-bd16-0090272ccb30"]}, {"action": "6c488338-8eec-4103-ad21-cd461ac2c472", "action-settings": {"enable-identity-captive-portal": false}, "comments": "FYI", "content": ["97aeb369-9aea-11d5-bd16-0090272ccb30"], "content-direction": "any", "content-negate": false, "custom-fields": {"field-1": "", "field-2": "", "field-3": ""}, "destination": ["b17d4573-ad1a-4126-ae6d-c874ea919cda", "5b78417c-64ed-4566-9c76-e4e1af25a9ae", "acb8d280-2ec4-46b1-be9f-c676fa255fb5"], "destination-negate": false, "domain": {"domain-type": "domain", "name": "SMC User", "uid": "41e821a0-3720-11e3-aa6e-0800200c9fde"}, "enabled": true, "hits": {"first-date": {"iso-8601": "2018-03-14T14:55-0500", "posix": 1521057347000}, "last-date": {"iso-8601": "2018-03-19T03:58-0500", "posix": 1521449932000}, "level": "low", "percentage": "0%", "value": 11801}, "install-on": ["6c488338-8eec-4103-ad21-cd461ac2c476"], "meta-info": {"creation-time": {"iso-8601": "2018-03-14T09:47-0500", "posix": 1521038846894}, "creator": "System", "last-modifier": "admin", "last-modify-time": {"iso-8601": "2018-08-31T16:17-0500", "posix": 1535750234317}, "lock": "unlocked", "validation-state": "ok"}, "name": "[email protected]", }
From the above JSON file my requirement to redirect keys {uid, name, rule-number, comments, destination, source, hits.last-date}, etc. with their values to CSV basically.
By following the below code, I was able to generate the CSV but seems that is only parsing header, nothing else.
import json
import csvdef get_leaves(item, key=None):if isinstance(item, dict):leaves = []for i in item.keys():leaves.extend(get_leaves(item[i], i))return leaveselif isinstance(item, list):leaves = []for i in item:leaves.extend(get_leaves(i, key))return leaveselse:return [(key, item)]with open('sample.json') as f_input, open('output.csv', 'wb') as f_output:
csv_output = csv.writer(f_output)
write_header = Truefor entry in json.load(f_input):leaf_entries = sorted(get_leaves(entry))if write_header:csv_output.writerow([k for k, v in leaf_entries])write_header = Falsecsv_output.writerows([v for k, v in leaf_entries.items()])
Please guide me as I am very much new to Python scripting.