Python Forum

Hello Friends,

I am still a learner of Python and I have a situation where I have to convert my CSV to Json. I understand how we can convert direct CSV row to json format. But I am struggling to understand how I can convert multiple rows in a column to multiple items in json. Please help.

Thanks
Below is my scenario.

Source CSV:


Type 1:
deptno, deptname, client name, client dob, client phone, client email
"100", "Sales", "ABC", "01011980", "0282200222;0729282929", "[email protected]"

Type 2:
deptno, deptname, client name, client dob, client phone, client email
"100", "Sales", "ABC", "01011980", "0282200222", "[email protected]"
"100", "Sales", "ABC", "01011980", "0729282929", "[email protected]"


Required Target in json:
{
"deptno": "100",
"deptname": "Sales",


"client": {
"name": "ABC",
"dob": "01011980",
"contact": [{
"TYPE": "PHONE",
"VALUE": "0282200222"
},
{
"TYPE": "PHONE",
"VALUE": "0729282929"
}
{
"TYPE": "email",
"VALUE": "[email protected]"
}
],
}
}

Note that there is no unique filed or unique combination of fields that can identify separate clients

Also, your JSON is not good format - I guess there would be multiple clients per department, so I guess you need JSON array for clients.

(Nov-02-2020, 09:53 AM)buran Wrote: [ -> ]Note that there is no unique filed or unique combination of fields that can identify separate clients

Also, your JSON is not good format - I guess there would be multiple clients per department, so I guess you need JSON array for clients.

Hi Buran,

Thanks for coming back. Assume I got one row for each client. Combination of deptno, client id is the unique key. I know how I can convert each row into jason, but only issue is around converting multiple phone numbers in one column delimiter ";", to multiple child items. Source CSV format is not an issue as its in my hand i can do however I want to generate it with. and once CSV is extracted, converting that into json is the main work. Any advise on source format can also be accommodated.

Type 1:
deptno, deptname, client id, client name, client dob, client phone, client email
"100", "Sales", "200", "ABC", "01011980", "0282200222;0729282929", "[email protected]"
"100", "Sales", "201", "BBC", "01011980", "0282200222;0729282929", "[email protected]"

Regards,
Suresh

iterate over rows and collect all phone numbers for a given client in a list and only then insert that client into the json object.
another option is change the JSON (if that is an option) and use a key so that you can access the elements and insert phone while iterating over lines.

(Nov-02-2020, 10:09 AM)buran Wrote: [ -> ]iterate over rows and collect all phone numbers for a given client in a list and only then insert that client into the json object.
another option is change the JSON (if that is an option) and use a key so that you can access the elements and insert phone while iterating over lines.

Thanks

Any code snippet please for this. I am not great at pyhton coding.

Suresh

Basically I would choose JSON to look like this

Output:
{
    "departments": [
        {
            "deptno": "100",
            "deptname": "Sales",
            "clients": [
                {
                    "client name": "ABC",
                    "client id": "101",
                    "client DOB": "01011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                },
                {
                    "client name": "DEF",
                    "client id": "102",
                    "client DOB": "03011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "mobile",
                            "value": "987654321"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                }
            ]
        },
        {
            "deptno": "200",
            "deptname": "Accounting",
            "clients": [
                {
                    "client name": "KLM",
                    "client id": "201",
                    "client DOB": "01011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                },
                {
                    "client name": "PQR",
                    "client id": "202",
                    "client DOB": "03011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "mobile",
                            "value": "987654321"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                }
            ]
        }
    ]
}

or

Output:
{
    "departments": {
        "100": {
            "deptno": "100",
            "deptname": "Sales",
            "clients": {
                "101": {
                    "client name": "ABC",
                    "client id": "101",
                    "client DOB": "01011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                },
                "102": {
                    "client name": "DEF",
                    "client id": "102",
                    "client DOB": "03011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "mobile",
                            "value": "987654321"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                }
            }
        },
        "200": {
            "deptno": "200",
            "deptname": "Accounting",
            "clients": {
                "201": {
                    "client name": "KLM",
                    "client id": "201",
                    "client DOB": "01011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                },
                "202": {
                    "client name": "PQR",
                    "client id": "202",
                    "client DOB": "03011980",
                    "contacts": [
                        {
                            "type": "email",
                            "value": "[email protected]"
                        },
                        {
                            "type": "mobile",
                            "value": "987654321"
                        },
                        {
                            "type": "phone",
                            "value": "123456789"
                        }
                    ]
                }
            }
        }
    }
}

the second one would allow easy access to each element