0

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.

PrayingMantis
  • 150
  • 2
  • 13
  • 1
    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 Apr 11 '21 at 13:25
  • @gold_cy Any idea how I could solve this problem as I am not able to edit the CSV file ? – PrayingMantis Apr 11 '21 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 Apr 11 '21 at 13:29
  • try this [answer](https://stackoverflow.com/a/66699446/12375044) 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 Apr 11 '21 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 Apr 11 '21 at 14:43
  • The "JSON" isn't JSON as well. it is the `str` of `dict`. JSON uses double quotes. – Mark Tolonen Apr 11 '21 at 17:53

1 Answers1

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)
Mark Tolonen
  • 166,664
  • 26
  • 169
  • 251
STerliakov
  • 4,983
  • 3
  • 15
  • 37
  • 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. – PrayingMantis Apr 11 '21 at 14:45