将json响应转换为CSV文件

我有以下json响应。

{"userId":"vendor","sessionId":"3d716be43d094fefa2261b5347a6127f","channelName":"/omsevents/usession/3d716be43d094fefa2261b5347a6127f","fadList":["NERemoteConfig","ShowSWStatus","esm_modify_erp","ManageStaticLink","ForceActivateSw","GetNeTime","AlarmDebounceModify","SetMeTime","DeleteME","esm_prov_tunnel","CreateJob","GlobalAlignDownwards","esm_prov_mclag","removeSubnet","MapNodeMovement","ModifyRNEIndex","ViewSwdlJobReport","GetSwPkg","AdvancedConstruct","wdm_performanceManagementFunction","Map_FUNCTION_admin_operator","ManageNEInventory","GetNeList","OSAddressRW","ShowInformations","SwStatusSwdlJob","CreateSwdlSession","RunPerfMon_15m","cpb_operationFunction","AbortSwdlSession","Cmd_MapInv_SbnListNodes","F_DELETE_CUSTOMER_TEMPLATE","esm_prov_npa","InternalCmd_MapView_View_ZoomIn","OSAddressRO","Cmd_TM_FUNCTION_allUser","esm_modify_lag","AlignUpwards","DeleteSwdlNes","InternalCmd_MapView_Obj_Undo","ShowBkupSession","CreateDynamicLink","ViewRestoreJobReport","DisplayRI","PathBuild","ManageMonitoring","ShowRestoreNes","ManageMeTime","SysMonRead","DeleteBkpNes","DeleteSwdlSession","RemoveGeneric","ShowSwdlNes","ManageNTP","Cmd_CLOG_Admin_Header_P","ShowEqp","DeleteBkpSession","DeleteRestoreSession","Cmd_CLOG_Admin_Header","ManageNEPopulation","GetSwStatusByType","ShowIsaSWStatus","MEAddressRW","ActivateSw","esm_generate_pm_report","Cmd_Inv_LinkListConnections","Cmd_MapNav_ObjPtrToNPAview","Cmd_CLOG_Ack","MSGUI_FUNCTION_ADMIN_TOPOLOGICALVIEWS","PingNE","esm_modify_mclag","AbortJob","ManualAlignDownwards","Cmd_MapInv_ListTopLevelNtws_PHY_TOPVIEW","Cmd_MapInv_SbnListChildSbns","ShowRestoreSession","Cmd_MapAct_SbnRemoveNode","F_ADD_CUSTOMER_TEMPLATE","ManageAccess","CreateBkupSessionWizard","CreateRestoreSessionWizard","ShowJobList","MigrateME","OpenJobWizard","Cmd_TM_FUNCTION_admin_construct","SysMonAdmin","ModifyLocation","wdm_neManagementFunction","OpenGetSwByTypeWizard","ModifyME","Cmd_MapNav_OpcToMap","NavigationToELM","Cmd_MapAct_SbnDeleteSbnAndSubordinates","AbortNe","Cmd_CLOG_User_Header","MultipleNeDownload","Cmd_TM_FUNCTION_admin_construct_operator","Admin","AddSwdlNes","saveSubnet","wdm_legacyManagementFunction","GetSwdl","Cmd_MapInv_SbnCreateMap","Cmd_MapAct_SbnCommitMapModify","Cmd_MapInv_ListTopLevelNtws_PHY_Test","esm_prov_customer","physconnInNet","UploadRemoteInventory","GetSwDetail","ActiveJob","NEInventory","ManageResynchro","RunPerfMon_hour","Operations","Cmd_MapWiz_ModifySubnet","AbortBkupSession","GetNeLabel","SetNeTime","InternalCmd_MapView_View_ZoomOut","NavigationToEquipments","ManageFilter","ViewBkupJobReport","DeleteSw","wdm_inventoryFunction","ManageLocalAccess","NetworkAddressRW","esm_job_reschedule","AddBkpNes","SysMonView","esm_design_template","Cmd_MapInv_PopulateSbnsInTree_PHY","addSubnet","InternalCmd_MapView_Obj_Redo","Cmd_MapInv_ShowOpcView","NetworkAddressRO","esm_design_feature","InternalCmd_MapView_View_ZoomBestFit","AlarmDebounceView","EditSwdlJobWizard","CreatePartialSwdlJob","ModifyACD","NavigationToURLs","EMLInventory","viewMap","Cmd_TM_FUNCTION_admin","esm_prov_service","Cmd_MapAct_SbnAddNode","DeleteRestoreNes","ActiveSoftware","NavigationToHostNe","AdvancedViewer","wdm_alarmManagementFunction","AllUsers","CreateSUBNE","ShowSwdlSession","EditJob","ManageSupervision","CreateNE","esm_modify_tunnel","ModifyUserLabel","Cmd_MapWiz_SbnCreateSimple","esm_modify_npa","GetNeType","Cmd_MapAct_DataSynchronize","OpenGetSwByNameWizard","InternalCmd_MapView_View_ChangeBackground","ResynchroAll","RunPerfMon_day","MulitpleNeBackup","ModifyComments","CreatePartialSwdlJobFromNeList","F_MODIFY_CUSTOMER_TEMPLATE","ManageLinkInventory","GetFtServer","esm_modify_service","ManageMib","EditRestorJobWizard","esm_deploy_networkConfig","Cmd_EventParameter","ShowJobStatus","ShowStatuses","addNode","Cmd_CLOG_User_Header_P","NavigationToAlarms","InternalCmd_MapView_Obj_Save","EditBkupJobWizard","InternalCmd_MapView_View_SwitchLayer","CreateRestoreSession","esm_job_delete","esm_prov_erp","Cmd_MapCrt_SbnCreate","Cmd_MapInv_ListObjsForCreateSbn","GetSwStatusByName","CreateSwdlSessionWizard","DirInventory","removeNode","ManageAdmin","esm_prov_lag","DeleteJob","CreateBkupSession","wdm_provisionFunction","ManageClone","modifySubnet","CommitSw","CreateME","ShowAlarms","InternalCmd_MapView_View_NormalSize","CreateRNE","InternalCmd_MapView_View_Miniature","ShowBackupNes","Construct","Cmd_Inv_SbnListPhyconnections","NERemoteConfig","ShowSWStatus","esm_modify_erp","ManageStaticLink","ForceActivateSw","GetNeTime","AlarmDebounceModify","SetMeTime","DeleteME","esm_prov_tunnel","CreateJob","GlobalAlignDownwards","esm_prov_mclag","removeSubnet","MapNodeMovement","ModifyRNEIndex","ViewSwdlJobReport","GetSwPkg","AdvancedConstruct","wdm_performanceManagementFunction","Map_FUNCTION_admin_operator","ManageNEInventory","GetNeList","OSAddressRW","ShowInformations","SwStatusSwdlJob","CreateSwdlSession","RunPerfMon_15m","cpb_operationFunction","AbortSwdlSession","Cmd_MapInv_SbnListNodes","F_DELETE_CUSTOMER_TEMPLATE","esm_prov_npa","InternalCmd_MapView_View_ZoomIn","OSAddressRO","Cmd_TM_FUNCTION_allUser","esm_modify_lag","AlignUpwards","DeleteSwdlNes","InternalCmd_MapView_Obj_Undo","ShowBkupSession","CreateDynamicLink","ViewRestoreJobReport","DisplayRI","PathBuild","ManageMonitoring","ShowRestoreNes","ManageMeTime","SysMonRead","DeleteBkpNes","DeleteSwdlSession","RemoveGeneric","ShowSwdlNes","ManageNTP","Cmd_CLOG_Admin_Header_P","ShowEqp","DeleteBkpSession","DeleteRestoreSession","Cmd_CLOG_Admin_Header","ManageNEPopulation","GetSwStatusByType","ShowIsaSWStatus","MEAddressRW","ActivateSw","esm_generate_pm_report","Cmd_Inv_LinkListConnections","Cmd_MapNav_ObjPtrToNPAview","Cmd_CLOG_Ack","MSGUI_FUNCTION_ADMIN_TOPOLOGICALVIEWS","PingNE","esm_modify_mclag","AbortJob","ManualAlignDownwards","Cmd_MapInv_ListTopLevelNtws_PHY_TOPVIEW","Cmd_MapInv_SbnListChildSbns","ShowRestoreSession","Cmd_MapAct_SbnRemoveNode","F_ADD_CUSTOMER_TEMPLATE","ManageAccess","CreateBkupSessionWizard","CreateRestoreSessionWizard","ShowJobList","MigrateME","OpenJobWizard","Cmd_TM_FUNCTION_admin_construct","SysMonAdmin","ModifyLocation","wdm_neManagementFunction","OpenGetSwByTypeWizard","ModifyME","Cmd_MapNav_OpcToMap","NavigationToELM","Cmd_MapAct_SbnDeleteSbnAndSubordinates","AbortNe","Cmd_CLOG_User_Header","MultipleNeDownload","Cmd_TM_FUNCTION_admin_construct_operator","Admin","AddSwdlNes","saveSubnet","wdm_legacyManagementFunction","GetSwdl","Cmd_MapInv_SbnCreateMap","Cmd_MapAct_SbnCommitMapModify","Cmd_MapInv_ListTopLevelNtws_PHY_Test","esm_prov_customer","physconnInNet","UploadRemoteInventory","GetSwDetail","ActiveJob","NEInventory","ManageResynchro","RunPerfMon_hour","Operations","Cmd_MapWiz_ModifySubnet","AbortBkupSession","GetNeLabel","SetNeTime","InternalCmd_MapView_View_ZoomOut","NavigationToEquipments","ManageFilter","ViewBkupJobReport","DeleteSw","wdm_inventoryFunction","ManageLocalAccess","NetworkAddressRW","esm_job_reschedule","AddBkpNes","SysMonView","esm_design_template","Cmd_MapInv_PopulateSbnsInTree_PHY","addSubnet","InternalCmd_MapView_Obj_Redo","Cmd_MapInv_ShowOpcView","NetworkAddressRO","esm_design_feature","InternalCmd_MapView_View_ZoomBestFit","AlarmDebounceView","EditSwdlJobWizard","CreatePartialSwdlJob","ModifyACD","NavigationToURLs","EMLInventory","viewMap","Cmd_TM_FUNCTION_admin","esm_prov_service","Cmd_MapAct_SbnAddNode","DeleteRestoreNes","ActiveSoftware","NavigationToHostNe","AdvancedViewer","wdm_alarmManagementFunction","AllUsers","CreateSUBNE","ShowSwdlSession","EditJob","ManageSupervision","CreateNE","esm_modify_tunnel","ModifyUserLabel","Cmd_MapWiz_SbnCreateSimple","esm_modify_npa","GetNeType","Cmd_MapAct_DataSynchronize","OpenGetSwByNameWizard","InternalCmd_MapView_View_ChangeBackground","ResynchroAll","RunPerfMon_day","MulitpleNeBackup","ModifyComments","CreatePartialSwdlJobFromNeList","F_MODIFY_CUSTOMER_TEMPLATE","ManageLinkInventory","GetFtServer","esm_modify_service","ManageMib","EditRestorJobWizard","esm_deploy_networkConfig","Cmd_EventParameter","ShowJobStatus","ShowStatuses","addNode","Cmd_CLOG_User_Header_P","NavigationToAlarms","InternalCmd_MapView_Obj_Save","EditBkupJobWizard","InternalCmd_MapView_View_SwitchLayer","CreateRestoreSession","esm_job_delete","esm_prov_erp","Cmd_MapCrt_SbnCreate","Cmd_MapInv_ListObjsForCreateSbn","GetSwStatusByName","CreateSwdlSessionWizard","DirInventory","removeNode","ManageAdmin","esm_prov_lag","DeleteJob","CreateBkupSession","wdm_provisionFunction","ManageClone","modifySubnet","CommitSw","CreateME","ShowAlarms","InternalCmd_MapView_View_NormalSize","CreateRNE","InternalCmd_MapView_View_Miniature","ShowBackupNes","Construct","Cmd_Inv_SbnListPhyconnections"],"nadString":"Voda unknown","userNadRole":"GLOBAL"}

我需要将其转换为一个csv文件,每一行都有标题,并在其下面是值。像这样

userId    sessionId    channelName            fadList   NEremote  ...
vendor     3d716be..   /omsevents/usession     so on     so on    ... 

我尝试了此解决方案,但无法正常工作。

def json_csv() :

    file = input("Please Enter new CSV file new  :")
    # Opening JSON file and loading the data
    # into the variable data
    with open(r"D:\json.txt") as json_file:
        data = json.load(json_file)

    employee_data = data

    # now we will open a file for writing
    data_file = open(file,'w')

    # create the csv writer object
    csv_writer = csv.writer(data_file)

    # Counter variable used for writing
    # headers to the CSV file
    count = 0

    for emp in data:
        if count == 0 :

            # Writing headers of CSV file
            header = emp.keys()
            csv_writer.writerow(header)
            count += 1

        # Writing data of CSV file
        csv_writer.writerow(emp.values())

    data_file.close()
json_csv()

更新1:

感谢Hozafya,他提供了以下解决方案:

from pandas.io.json import json_normalize
x = open("test.txt").readline()
df = json_normalize(x)
df.to_csv("file.csv")

该脚本适用于前3个项目。从列表开始,所有的所有数据都集中在一列中。所以我需要数据例如如下:

excel sheet example image

谢谢。

评论
哎呦喂
哎呦喂

you can easily do that using pandas:

from pandas.io.json import json_normalize
x = open("test.txt").readline()
df = json_normalize(x)
df.to_csv("file.csv")
点赞
评论