python读取txt数据，转换到excel表中

接收后台模拟数据，存放在txt文件中，需要转换到excel中

txt中的数据格式为：

 recv message - 82815: {"export1": 0, "export0": 0, "export3": 1, "export2": 0, "export5": 0, "export4": 0, "clean1_5": 0, "clean1_4": 2, "clean1_1": 0, "clean1_3": 3, "clean1_2": 0, "last_elasticity": "9.17999982834", "massage1": 1, "massage0": 0, "clean3_3": 0, "clean3_2": 3, "clean3_1": 0, "last_time": "1481772013", "clean3_5": 0, "clean3_4": 2, "light2": 0, "spray2": 0, "spray1": 0, "this_oil": 22.24000015258789, "light1": 0, "this_water": 39.23999977111816, "this_elasticity": 9.299999713897705, "ultrasonic1": 0, "last_water": "23.2400001526", "ultrasonic0": 0, "import2": 0, "import3": 0, "import0": 1, "import1": 0, "import4": 0, "import5": 0, "light3": 1, "this_time": "2016-12-15 11:45:27", "ultrasonic2": 0, "this_skin_type": 3, "last_skin_type": "7", "clean2_2": 3, "clean2_3": 2, "ultrasonic3": 1, "clean2_1": 0, "ultrasonic5": 0, "ultrasonic4": 0, "clean2_4": 0, "clean2_5": 0, "clean5_1": 3, "light0": 0, "clean5_3": 2, "clean5_2": 0, "clean5_5": 0, "clean5_4": 0, "spray3": 1, "clean4_1": 3, "clean4_2": 0, "clean4_3": 0, "clean4_4": 2, "clean4_5": 0, "spray4": 0, "last_oil": "17.4400001526", "age": 0}

转换到excel中：

转换方法一： 需要每次手动输入数据

#coding=utf-8
import re 
import json
fileobj = open("data.csv", "a")
  
message = raw_input("input:")
message = json.loads(message)
message = sorted(message.items(), key=lambda x: x[0])
keys = [str(i[0]) for i in message]
values = [str(i[1]) for i in message]
fileobj.write(",".join(values) + "\n")
a = ",".join(values),
print type(a),a,len(a)
print  str(values)
fileobj.write(str(values) + "\n")
print "finish "
fileobj.close()

转化方法二：

#-*- coding:utf-8 -*-
import sys
import csv 
import re
import json

with open('test.txt') as f:
content = f.read()
f.close()
pattern = re.compile(r'.*?(\{.*?\})',re.S)
a = re.findall(pattern,content)
dicts = map(lambda x:eval(x),[x for x in a])
# for (k,v) in dicts[0].items():
# print k,v
print dicts[0]
csvfile = file('csv_test.csv','wb')
writer = csv.writer(csvfile)
writer.writerow([x for x in dicts[0].keys()])

for i in range(1,len(dicts)):
writer.writerow([x for x in dicts[i].values()])
csvfile.close()

转换后的excel格式，在excel中参考

补充点：对列表中的字典元素进行字段排序

#-*- coding:utf-8 -*-
import sys
# reload(sys)
# sys.setdefaultencoding("utf-8")
import csv 
import re
import json
with open('test.txt') as f:
content = f.read()
f.close()
pattern = re.compile(r'.*?(\{.*?\})',re.S)
a = re.findall(pattern,content)
dicts = map(lambda x:eval(x),[x for x in a])
# for (k,v) in dicts[0].items():
# print k,v
print dicts[0]
sort_a = sorted(dicts[0].items(),key=lambda x:x[0])
dicts = map(lambda x: sorted(x.items(),key=lambda x:x[0]),dicts)
print sort_a[0][0]


csvfile = file('csv_test.csv','wb')
writer = csv.writer(csvfile)
writer.writerow([x[0] for x in dicts[0]])


for i in range(1,len(dicts)):
writer.writerow([x[1] for x in dicts[i]])
csvfile.close()