Json文件转Excel表格

这里写自定义目录标题

• • 样本数据
  • 样本数据说明
  • 转换
  • 执行转换
  • 转换效果

样本数据

{"_id":"AXtapGbijcEAuoI0lDsp","_index":"person_address_label_info_master","_score":null,"_source":{"AGE":48,"BIRTHDAY":"1973","BPLACE":"吉林省吉林市磐石县","IDNO":"xxxxx","IDTYPE":"01","QUERY_STRING":"  吉林省吉林市磐石县   48 73 1973 ","RNAME":"崔太花","SEX":"女"},"_type":"a","sort":[6754190]}
{"_id":"AXtapGbijcEAuoI0lDsp","_index":"person_address_label_info_master","_score":null,"_source":{"AGE":48,"BIRTHDAY":"1973","BPLACE":"吉林省吉林市磐石县","IDNO":"xxxxx","IDTYPE":"01","QUERY_STRING":"  吉林省吉林市磐石县   48 73 1973 ","RNAME":"崔太花","SEX":"女"},"_type":"a","sort":[6754190]}
{"_id":"AXtapGbijcEAuoI0lDsp","_index":"person_address_label_info_master","_score":null,"_source":{"AGE":48,"BIRTHDAY":"1973","BPLACE":"吉林省吉林市磐石县","IDNO":"xxxxx","IDTYPE":"01","QUERY_STRING":"  吉林省吉林市磐石县   48 73 1973 ","RNAME":"崔太花","SEX":"女"},"_type":"a","sort":[6754190]}
{"_id":"AXtapGbijcEAuoI0lDsp","_index":"person_address_label_info_master","_score":null,"_source":{"AGE":48,"BIRTHDAY":"1973","BPLACE":"吉林省吉林市磐石县","IDNO":"xxxxx","IDTYPE":"01","QUERY_STRING":"  吉林省吉林市磐石县   48 73 1973 ","RNAME":"崔太花","SEX":"女"},"_type":"a","sort":[6754190]}
{"_id":"AXtapGbijcEAuoI0lDsp","_index":"person_address_label_info_master","_score":null,"_source":{"AGE":48,"BIRTHDAY":"1973","BPLACE":"吉林省吉林市磐石县","IDNO":"xxxxx","IDTYPE":"01","QUERY_STRING":"  吉林省吉林市磐石县   48 73 1973 ","RNAME":"崔太花","SEX":"女"},"_type":"a","sort":[6754190]}

样本数据说明

1. json文件每行是json格式，但json格式不标准，需要额外处理
2. json文件整体不是标准json数组，需解析后转成json数组再转excel文件

转换

import json
import pandas as pd
import openpyxlf = open("files/公民信息.json", "r", encoding="utf-8")output_data = []
while True:line = f.readline()if not line:break# 糾正錯誤的json格式line = line.replace('"{', '{')line = line.replace('}"', '}')# 字符串轉jsonjson_dict = json.loads(line)# 提取需要導出到excel表格的信息output_data.append({"姓名": json_dict['_source']['RNAME'],"性别": json_dict['_source']['SEX'],"年龄": json_dict['_source']['AGE'],"出生年份": json_dict['_source']['BIRTHDAY'],"身份证号": json_dict['_source']['IDNO'],"地址": json_dict['_source']['QUERY_STRING'],})print(output_data)# 轉換成表格
df = pd.DataFrame(output_data)
df.to_excel("公民信息.xlsx")
print(output_data)

执行转换

第一步：首先安装脚本所需依赖库
pip3 install pandas
pip3 install openpyxl第二步：其次把3个全量的需要转换的json文件替换到files目录里面
公民信息转表格，执行python3 converter_information.py然后会生成“公民信息.xlsx”

转换效果