How to write JSON **text** to Excel file?

Question

Is there any way in Python to write JSON text to an Excel file?

Normally I would load JSON response into Pandas dataframe and write the dataframe to Excel:

import pandas
form pandas.io.json import json_normalize
import requests

def df_to_excel(df, filename):
    writer = pandas.ExcelWriter(filename)
    df.to_excel(writer, 'Sheet1')
    writer.save()

response = requests.get(url, params).json()
df = json_normalize(response)
df_to_excel(df, 'Response.xlsx')

But this requires converting JSON text to Python object, thus replacing " with ', false with False, null with None etc, and I don't want that.

user_7281684 · Answer

You can convert json to dictionary first and then convert to excel file as suggested here Write dictionary values in an excel file:

import json
import xlsxwriter
import requests

response = requests.get(url, params)
d = json.loads(response.text)

workbook = xlsxwriter.Workbook('data.xlsx')
worksheet = workbook.add_worksheet()

row = 0
col = 0

for key in d.keys():
    row += 1
    worksheet.write(row, col, json.dumps(key))
    for item in d[key]:
        worksheet.write(row, col + 1, json.dumps(item))
        row += 1

workbook.close()

The key is using json.dumps() that converts pythonic format back to json format. For example, json.dumps(None) returns 'null'

How to write JSON text to Excel file?

1 Answers