May-20-2022, 10:47 PM
Want to convert Sample JSON data into CSV file using python. I am retrieving JSON data from API. As my JSON has nested objects, so it normally cannot be directly converted to CSV.I don't want to do any hard coding and I want to make a python code fully dynamic.
So, I have written a function that flatten my JSON Data but I am not able to work out how to iterate all records, finding relevant column names and then output those data into CSV.
In the Sample JSON file I have mentioned only 2 records but in actual there are 100 records. Sample JSON Look like this:
Any help?
So, I have written a function that flatten my JSON Data but I am not able to work out how to iterate all records, finding relevant column names and then output those data into CSV.
In the Sample JSON file I have mentioned only 2 records but in actual there are 100 records. Sample JSON Look like this:
[ { "id":"Random_Company_57", "unid":"75", "fieldsToValues":{ "Email":"None", "occupation":"SO1 Change", "manager":"None", "First Name":"Bells", "employeeID":"21011.0", "loginRequired":"true", "superUser":"false", "ldapSuperUser":"false", "archived":"true", "password":"None", "externalUser":"false", "Username":"Random_Company_57", "affiliation":"", "Phone":"+16 22 22 222", "unidDominoKey":"", "externalUserActive":"false", "secondaryOccupation":"SO1 Change", "retypePassword":"None", "Last Name":"Christmas" }, "hierarchyFieldAccess":[ ], "userHierarchies":[ { "hierarchyField":"Company", "value":"ABC Company" }, { "hierarchyField":"Department", "value":"gfds" }, { "hierarchyField":"Project", "value":"JKL-SDFGHJW" }, { "hierarchyField":"Division", "value":"Silver RC" }, { "hierarchyField":"Site", "value":"SQ06" } ], "locale":{ "id":1, "dateFormat":"dd/MM/yyyy", "languageTag":"en-UA" }, "roles":[ "User" ], "readAccessRoles":[ ], "preferredLanguage":"en-AU", "prefName":"Christmas Bells", "startDate":"None", "firstName":"Bells", "lastName":"Christmas", "fullName":"Christmas Bells", "lastModified":"2022-02-22T03:47:41.632Z", "email":"None", "docNo":"None", "virtualSuperUser":false }, { "id":"[email protected]", "unid":"98", "fieldsToValues":{ "Email":"[email protected]", "occupation":"SO1 Change", "manager":"None", "First Name":"Bells", "employeeID":"21011.0", "loginRequired":"false", "superUser":"false", "ldapSuperUser":"false", "archived":"false", "password":"None", "externalUser":"false", "Username":"[email protected]", "affiliation":"", "Phone":"+16 2222 222 222", "unidDominoKey":"", "externalUserActive":"false", "secondaryOccupation":"SO1 Change", "retypePassword":"None", "Last Name":"Christmas" }, "hierarchyFieldAccess":[ ], "userHierarchies":[ { "hierarchyField":"Company", "value":"ABC Company" }, { "hierarchyField":"Department", "value":"PUHJ" }, { "hierarchyField":"Project", "value":"RPOJ-SDFGHJW" }, { "hierarchyField":"Division", "value":"Silver RC" }, { "hierarchyField":"Site", "value":"SQ06" } ], "locale":{ "id":1, "dateFormat":"dd/MM/yyyy", "languageTag":"en-UA" }, "roles":[ "User" ], "readAccessRoles":[ ], "preferredLanguage":"en-AU", "prefName":"Christmas Bells", "startDate":"None", "firstName":"Bells", "lastName":"Christmas", "fullName":"Christmas Bells", "lastModified":"2022-03-16T05:04:13.085Z", "email":"[email protected]", "docNo":"None", "virtualSuperUser":false } ]What I have tried.
def flattenjson(b, delim): val = {} for i in b.keys(): if isinstance(b[i], dict): get = flattenjson(b[i], delim) for j in get.keys(): val[i + delim + j] = get[j] else: val[i] = b[i] print(val) return val json=[{Sample JSON String that mentioned above}] flattenjson(json,"__")I don't know it is a right way to deal this problem or not? My final aim is that to convert all the above json data into a csv file.
Any help?