How can I convert a CSV file to a JSON file with a nested json object using Python?

Question

I am stuck with a problem where I don't know how I can convert a "nested JSON object" inside a CSV file into a JSON object.

So I have a CSV file with the following value:

data.csv

1, 12385, {'message': 'test 1', 'EngineId': 3, 'PersonId': 1, 'GUID': '0ace2-02d8-4eb6-b2f0-63bb10829cd4s56'}, 6486D, TestSender1
2, 12347, {'message': 'test 2', 'EngineId': 3, 'PersonId': 2, 'GUID': 'c6d25672-cb17-45e8-87be-46a6cf14e76b'}, 8743F, TestSender2

I wrote a python script that converts this CSV file into a JSON file inside an array. This I did with the following python script

csvToJson.py

import json
import csv


with open("data.csv","r") as f:
    reader = csv.reader(f)
    data = []
    for row in reader:
        data.append({"id": row[0],
        "receiver": row[1],
        "payload": row[2],
        "operator": row[3],
        "sender": row[4]})

with open("data.json", "w") as f:
    json.dump(data, f, indent=4)

The problem I'm facing is that I'm not getting the right values inside "payload", which I would like to be a nested JSON object. The result I get is the following:

data.json

[
    {
        "id": "1",
        "receiver": " 12385",
        "payload": " {'message': 'test 1'",
        "operator": " 'EngineId': 3",
        "sender": " 'PersonId': 1"
    },
    {
        "id": "2",
        "receiver": " 12347",
        "payload": " {'message': 'test 2'",
        "operator": " 'EngineId': 3",
        "sender": " 'PersonId': 2"
    }
]

So my question is, how can I create a nested JSON object for the "payload" while I'm doing the conversion from CSV to JSON? I think the main problem is that it is seen as a string and not as an object.

actually it is because it is seeing a , in your json string which signifies a field break hence you don't get the whole string — gold_cy
– gold_cy, Commented Apr 11, 2021 at 13:25
@gold_cy Any idea how I could solve this problem as I am not able to edit the CSV file ? — PrayingMantis
– PrayingMantis, Commented Apr 11, 2021 at 13:27
if the JSON field is not quoted properly then I don't think there is a good way to solve this, you would have to write out the file to another file to properly parse it, for example changing the delimiter to | — gold_cy
– gold_cy, Commented Apr 11, 2021 at 13:29
try this answer read line by line if your file is not too big it works as expected for your data it will split by comma but ignore comma inside parenthesis. — pb36
– pb36, Commented Apr 11, 2021 at 13:43
This has very little to do with JSON. Your problem is that your file isn't a proper type of CSV, because the , separator is also used inside actual fields without any kind of escapement. For that reason, a regular CSV parser will fail to parse that input data. You'll probably have to resort to manual parsing. — Ulrich Eckhardt
– Ulrich Eckhardt, Commented Apr 11, 2021 at 14:43

Mark Tolonen · Accepted Answer · 2021-04-11 18:14:57Z

1

Try the following. You can just do everything as previously, but merge back all elements that were in 3rd column and load it via ast.literal_eval.

import json
import csv
import ast    

with open("data.csv","r") as f:
    reader = csv.reader(f,skipinitialspace=True)
    data = [{"id": ident,
             "receiver": rcv,
             "payload": ast.literal_eval(','.join(payload)),
             "operator": op,
             "sender": snd}
            for ident,rcv,*payload,op,snd in reader]

with open("data.json", "w") as f:
    json.dump(data, f, indent=4)

edited Apr 11, 2021 at 18:14

Mark Tolonen

181k26 gold badges182 silver badges278 bronze badges

answered Apr 11, 2021 at 14:34

STerliakov

8,8873 gold badges27 silver badges59 bronze badges

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

1 Comment

PrayingMantis Over a year ago

Thank you very much, this is exactly what I needed. At first, I was thinking to work with something like "payload": ast.literal_eval(row[2]), but it gave me some EOF errors, but your solution made it work perfectly.

Collectives™ on Stack Overflow

How can I convert a CSV file to a JSON file with a nested json object using Python?

data.csv

csvToJson.py

data.json

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

data.csv

csvToJson.py

data.json

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related