Example of requesting JSON data from a URL and converting to CSV

README.md

Download a JSON list of object and write to CSV

Requires Python 3.

Usage

Linux

python3 json_to_csv.py https://gist.githubusercontent.com/walkermatt/19b1108ca9bee94745810f4c39c32165/raw/cities.json /tmp/cities.csv

Windows

C:\python3\python.exe E:\iShareData\Utilities\json_to_csv.py https://gist.githubusercontent.com/walkermatt/19b1108ca9bee94745810f4c39c32165/raw/cities.json E:\Temp\cities.csv

cities.json

[
    {
        "city": "London",
        "country": "United Kingdom",
        "iso2": "GB",
        "lat": 51.5072,
        "lng": -0.1275
    },
    {
        "city": "Birmingham",
        "country": "United Kingdom",
        "iso2": "GB",
        "lat": 52.4800,
        "lng": -1.9025
    }
]

json_to_csv.py

"""
Converts a JSON array of object downloaded from url to CSV, output to the specified file or stdout if - is specified

Usage: json_to_csv.py url

url           URL from which to fetch JSON
csv_filename  Path to CSV file to output results to or - to write to stdout

"""

import csv
import io
import json
import sys
import urllib.request


def main(url):

    # Download JSON from the specified URL
    with urllib.request.urlopen(url) as response:
        content = response.read()

    # Parse the content into a list of results, attempt to determine the content encoding
    # from the response
    results = json.loads(content.decode(response.info().get_content_charset('utf-8')))

    # Create a list of column names, assumes all objects have the same properties
    columns = results[0].keys()

    # Open the file at path csv_filename for writing or write to stdout
    with open(csv_filename, 'w', newline='') if csv_filename != '-' else sys.stdout as csv_file:

        # Create a dictionary writer instance which we will use to write the header and
        # a row per result
        writer = csv.DictWriter(csv_file, columns, lineterminator='\n')

        # Write the header (columns) followed by a row per result
        writer.writeheader()
        for result in results:
            writer.writerow(result)


if __name__ == "__main__":
    if len(sys.argv) > 2:
        url = sys.argv[1]
        csv_filename = sys.argv[2]
        main(url)
    else:
        sys.stderr.write(__doc__)