现在手里有一堆JSON格式的数据，如何转化成excel？

测试了一下，office tool plus也支持json文件转为xlsx等格式，但只局限于导入数据，格式无法保证。

导入后

打开后的格式

用textsplit函数处理下就能分开。

取部分数据样本：

{"CampaignStates":[{"CampaignId":"437a3ef2-79ff-4d94-b4fc-498b3593610d","LastNominationTimeUtc":"2023-01-30T14:39:28Z","LastNominationBuildNumber":"16.0.16124.20000","DeleteAfterSecondsWhenStale":1209600,"ForceCandidacy":false,"IsCandidate":true,"DidCandidateTriggerSurvey":false,"LastSurveyActivatedTimeUtc":"1601-01-01T00:00:00Z"}]}

公式处理：

=SUBSTITUTE(TEXTSPLIT(A1,""""&":",{"':'","','","[","]",",","{","}"},1,,""),"""","")

pandas处理

有一些包含空数据的文件会无法转换，大部分文件都可成功转换。

import pandas as pd
import os
path = "D:/Users/Administrator/Desktop/New folder (15)"
filelist = os.listdir(path)