1. json to excel
1 #!/usr/bin/python 2 # encoding: utf-8 3 # lib: pandas, flatten_json 4 # author: Chita 5 import json 6 import os 7 import time 8 import pandas as pd 9 from flatten_json import flatten 10 11 12 def json_to_excel(filename): 13 all_json_data_list = [] 14 all_json_keys_list = [] 15 16 # get all json content 17 with open(filename, "r", encoding="utf-8") as f: 18 for data in f: 19 json_data = json.loads(data) 20 # use flatten_json lib, flatten json in one line 21 ret = flatten(json_data) 22 all_json_data_list.append(ret) 23 all_json_keys_list.extend(ret.keys()) 24 25 # get all json keys as csv header 26 csv_header_list = list(set(all_json_keys_list)) 27 # keep original order 28 csv_header_list.sort(key=all_json_keys_list.index) 29 30 csv_data_list = [] 31 32 for data in all_json_data_list: 33 # fill the missing field, set value "" 34 _header = data.keys() 35 different_keys = list(set(csv_header_list).difference(set(_header))) 36 37 for key in different_keys: 38 data[key] = "" 39 csv_data_list.append(data) 40 41 # use pandas write the flatten json to excel 42 df = pd.DataFrame(csv_data_list, columns=csv_header_list) 43 df.to_excel(f"{filename.split('.')[0]}_Excel_Result_{int(time.time())}.xlsx", index=False, header=csv_header_list) 44 45 46 def start(json_path): 47 # folder 48 if os.path.isdir(json_path): 49 files = os.listdir(json_path) 50 for f in files: 51 json_file = os.path.join(json_path, f) 52 json_to_excel(json_file) 53 54 # json file 55 if os.path.isfile(json_path): 56 json_to_excel(json_path) 57 58 59 if __name__ == '__main__': 60 start("test.txt")
2. json to csv
1 #!/usr/bin/python 2 # encoding: utf-8 3 # lib: pandas, flatten_json 4 # author: Chita 5 import json 6 import os 7 import time 8 import pandas as pd 9 from flatten_json import flatten 10 11 12 def json_to_excel(filename): 13 all_json_data_list = [] 14 all_json_keys_list = [] 15 16 # get all json content 17 with open(filename, "r", encoding="utf-8") as f: 18 for data in f: 19 json_data = json.loads(data) 20 # use flatten_json lib, flatten json in one line 21 ret = flatten(json_data) 22 all_json_data_list.append(ret) 23 all_json_keys_list.extend(ret.keys()) 24 25 # get all json keys as csv header 26 csv_header_list = list(set(all_json_keys_list)) 27 # keep original order 28 csv_header_list.sort(key=all_json_keys_list.index) 29 30 csv_data_list = [] 31 32 for data in all_json_data_list: 33 # fill the missing field, set value "" 34 _header = data.keys() 35 different_keys = list(set(csv_header_list).difference(set(_header))) 36 37 for key in different_keys: 38 data[key] = "" 39 csv_data_list.append(data) 40 41 # use pandas write the flatten json to csv 42 df = pd.DataFrame(csv_data_list, columns=csv_header_list) 43 df.to_csv(f"{filename.split('.')[0]}_CSV_Result_{int(time.time())}.csv", index=False) 44 45 46 def start(json_path): 47 # folder 48 if os.path.isdir(json_path): 49 files = os.listdir(json_path) 50 for f in files: 51 json_file = os.path.join(json_path, f) 52 json_to_excel(json_file) 53 54 # json file 55 if os.path.isfile(json_path): 56 json_to_excel(json_path) 57 58 59 if __name__ == '__main__': 60 start("test.txt")
3. 测试数据 test.txt
{"a": 1, "b": {"b_1": 2}, "c": [1, 2, 3], "d": [{"d_2": "d2"}], "e": {"e_1": ["e1", "e2"]}}
{"a": 2, "b": {"b_1": 2, "b_2": 3}, "d": [{"d_1": "d1"}], "e": {"e_1": ["e1", "e2"]}}