Posts: 3
Threads: 1
Joined: Nov 2020
Nov-02-2020, 09:16 AM
(This post was last modified: Nov-02-2020, 09:17 AM by Sureshonly.)
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]"
}
],
}
}
Posts: 8,165
Threads: 160
Joined: Sep 2016
Nov-02-2020, 09:53 AM
(This post was last modified: Nov-02-2020, 09:53 AM by buran.)
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.
Posts: 3
Threads: 1
Joined: Nov 2020
(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
Posts: 8,165
Threads: 160
Joined: Sep 2016
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.
Posts: 3
Threads: 1
Joined: Nov 2020
(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
Posts: 8,165
Threads: 160
Joined: Sep 2016
Nov-02-2020, 10:20 AM
(This post was last modified: Nov-02-2020, 10:20 AM by buran.)
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
|
