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:
What I have tried.
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?
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
[ { "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":"xyz.abc@safe.net", "unid":"98", "fieldsToValues":{ "Email":"xyz.abc@safe.net", "occupation":"SO1 Change", "manager":"None", "First Name":"Bells", "employeeID":"21011.0", "loginRequired":"false", "superUser":"false", "ldapSuperUser":"false", "archived":"false", "password":"None", "externalUser":"false", "Username":"xyz.abc@safe.net", "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":"xyz.abc@safe.net", "docNo":"None", "virtualSuperUser":false }] |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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,"__") |
Any help?
