JSON Parser – Python Code – Convert JSON To Excel CSV

JSON To CSV – Python Json Tutorial

Use this code to parse Json to csv or Excel. Actually, this code write to a csv file, which can be opened in a Excel file as well. So, both are same CSV or Excel.

If you are not sure about how to read or write data to a CSV file from Python, then refer this page for more info. If you are looking to parse Json using a Excel vba code directly, then here is complete guide & code.

Python JSON – Example

Copy this sample file and store it in D drive as Json.txt file. This file will be given as input for the Json parser code.

[
    {
        "id": 10,
        "url": "https://offietricks.com",
        "category": {
            "name": "Python: JSON to CSV",
            "tags": "Python, JSON, CSV , Writerow, Open",
            "author": "author@officetricks.com"
        }
    },
    {
        "id": 11,
        "url": "http://buddingday.com",
        "category": {
            "name": "Art and Entertainment",
            "tags": "art, tech, entertainment",
            "author": "author@officetricks.com"
        }
    },
    {
        "id": 13,
        "url": "http://financeicon.com",
        "category": {
            "name": "Finance, Investment, Expenses, Business",
            "tags": "Market, Expenses, Business, Finance",
            "author": "author@officetricks.com"
        }
    }
]

JSON Parser Code

Just check that you have the sample file in this path to execute this code. “d:/json.txt”. If you already have a json file, then replace the file path in the below code then press F5 to execute this code in the Python IDE.

#--Code from Officetricks.com
#--JSON PARSER: Json to CSV or Excel Converter
import csv
import json

#--Open JSON File and Load to Parser
f=open('d:/JSON.txt')
jsonData=json.load(f)
f.close

#--Open CSV File in Output Mode
with open('d:/file.csv', 'w') as f:
    JsonToCSV = csv.writer(f)
    JsonToCSV.writerow(["id", "url", "category name", "tags", "author"])

    #--Loop thru Each Object or Collection in JSON file
    for item in jsonData:
            JsonToCSV.writerow([item["id"],
                item["url"],
                item["category"]["name"],
                item["category"]["tags"],
                item["category"]["author"]])

Once this file is written to the CSV, open the output file in a Notepad, Textpad or Excel app to veiw the JSON data being converted to different format.

Though it is a Json parser, one should know the field names and how they are arranged in the JSON file. We will be able to convert or read each node only if we know the node names if it is arranged in multiple levels.

For examplel, in the above sample file, we have three fields inside category. So, we have to explicitly specify the name of each node to get its value.