[FIXED] How to convert a csv file into a json formatted file?

Issue

Most of the samples here show hard-coded columns and not an iteration. I have 73 columns I want iterated and expressed properly in the JSON.

import csv
import json


CSV_yearly = r'C:\path\yearly.csv'
JSON_yearly = r'C:\path\json_yearly.json'

with open(CSV_yearly, 'r') as csv_file:
    reader = csv.DictReader(csv_file)
    with open(JSON_yearly, 'w') as json_file:
        for row in reader:
            json_file.write(json.dumps(row) + ',' + '\n')

print "done"

Though this creates a json file it does one improperly. I saw examples where an argument inside reader requested a list, but i don't want to type out 73 columns from the csv. My guess is the line of code goes between the start of with and reader.

Solution

Your code creates each line in the file as a separate JSON object (sometimes called JsonL or json-lines format). Collect the rows in a list and then serialise as JSON:

with open(CSV_yearly, 'r') as csv_file:
    reader = csv.DictReader(csv_file)
    with open(JSON_yearly, 'w') as json_file:
        rows = list(reader)
        json.dump(rows, json_file)

Note that some consumers of JSON expect an object rather than a list as an outer container, in which case your data would have to be

rows = {'data': list(reader)}

Update: - questions from comments

• Do you know why the result did not order my columns accordingly?

csv.DictReader uses a standard Python dictionary to create rows, so the order of keys is arbitrary in Python versions before 3.7. If key order must be preserved, try using an OrderedDict:

from collections import OrderedDict

out = []
with open('mycsv.csv', 'rb') as f:
    reader = csv.reader(f)
    headings = next(reader) # Assumes first row is headings, otherwise supply your own list
    for row in reader:
        od = OrderedDict(zip(headings, row))
        out.append(od)

# dump out to file using json module

Be aware that while this may output json with the required key order, consumers of the json are not required to respect it.

• Do you also know why my values in the json were converted into string and not remain as a number or without parenthesis.

All values from a csv are read as strings. If you want different types then you need to perform the necessary conversions after reading from the csv file.

Answered By - snakecharmerb