Does anyone know how can I convert JSON to XLS in Python?
I know that it is possible to create xls files using the package xlwt in Python.
What if I want to convert a JSON data file to XLS file directly?
Is there a way to archive this?
5 Answers
Using pandas (0.15.1) and openpyxl (1.8.6):
import pandas
pandas.read_json("input.json").to_excel("output.xlsx")
1 Comment
Dr. Essen
this command puts all the key:value pairs of a particulat {set} in the same row.
I usually use tablib for this use. Its pretty simple to use: https://pypi.python.org/pypi/tablib/
1 Comment
Silidrone
please note that this link is to a very old version of tablib (probably newest at the time) and it causes problems with newest versions of python, instead install tablib 3.0.0 version
If your json file is stored in some directory then,
import pandas as pd
pd.read_json("/path/to/json/file").to_excel("output.xlsx")
If you have your json within the code then, you can simply use DataFrame
json_file = {'name':["aparna", "pankaj", "sudhir", "Geeku"],'degree': ["MBA", "BCA", "M.Tech", "MBA"],'score':[90, 40, 80, 98]}
df = pd.DataFrame(json_file).to_excel("excel.xlsx")
1 Comment
Jinna Baalu
I am getting this error ' If using all scalar values, you must pass an index'
In case someone wants to do output to Excel as a stream using Flask-REST
Pandas versions:
json_payload = request.get_json()
with NamedTemporaryFile(suffix='.xlsx') as tmp:
pandas.DataFrame(json_payload).to_excel(tmp.name)
buf = BytesIO(tmp.read())
response = app.make_response(buf.getvalue())
response.headers['content-type'] = 'application/octet-stream'
return response
and OpenPyXL version:
keys = []
wb = Workbook()
ws = wb.active
json_data = request.get_json()
with NamedTemporaryFile() as tmp:
for i in range(len(json_data)):
sub_obj = json_data[i]
if i == 0:
keys = list(sub_obj.keys())
for k in range(len(keys)):
ws.cell(row=(i + 1), column=(k + 1), value=keys[k]);
for j in range(len(keys)):
ws.cell(row=(i + 2), column=(j + 1), value=sub_obj[keys[j]]);
wb.save(tmp.name)
buf = BytesIO(tmp.read())
response = app.make_response(buf.getvalue())
response.headers['content-type'] = 'application/octet-stream'
return response
Comments
If you want to convert any JSON (.json) file into Microsoft Excel you can try the below code snippet. And you are getting
Value error: trailing data
Example: your file name is input.json.
import pandas as pd
pd.read_json(“input.json”, lines = True).to_excel(“output.xlsx”)
output.xlsx will be your required file
pip install hfexcel