3

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.

Malik Asad
  • 441
  • 4
  • 15
barciewicz
  • 3,511
  • 6
  • 32
  • 72
  • 1
    I suggest just forgetting it's JSON and treating it as a simple string, you can write a string into an Excel cell quite easily. Unless you want to do something more complex? You didn't make clear exactly what the output should look like. – ADyson Nov 09 '18 at 12:27
  • I would prefer to have JSON keys as columns in Excel and values as rows, so it is easier to just load the file in a dataframe from another module and also for easy browsing of the data. But your method would work as last resort - thanks. – barciewicz Nov 09 '18 at 13:18

1 Answers1

2

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'

DmytroSytro
  • 579
  • 4
  • 15
  • 1
    Thanks. Would it be possible to put JSON into Pandas dataframe as text? I could then easily write the dataframe to Excel. – barciewicz Nov 09 '18 at 17:01
  • 1
    As far as I know there's no library that can help you do that. However, you could iterate keys and values of dictionary d applying json.dumps to them and then construct DataFrame from dictionary using pandas.DataFrame.from_dict method. I'm in the road now, I'll add code later if needed. Also add the example of json file you need to convert – DmytroSytro Nov 09 '18 at 17:35
  • Thanks Dmytro, that is what I thought as well. – barciewicz Nov 09 '18 at 17:39