CODEWITHSUNDEEP
jsonpython-2.7xls
Christian
Christian

Reputation: 43

Mapping a json file to excel with python

I would like to take a json format file and map it into an xls file. i.e. in the input file 

{
  "results": [
    {
      "promo_video": "https:\/\/www.youtube.com\/embed\/Tztev0Q-CN8?ps=play&vq=large&rel=0&autohide=1&showinfo=0&autoplay=1",
      "iap": true,
      "downloads": "10,000,000"
    }
  ]
}

to be presented in an excel file: column headers: promo video| iap | downloads and the respective values of each column

Would be helpful to know a recommended way and syntax example.

Thank you.

Upvotes: 3

Views: 18134

Answers (2)

Dipankar Nalui
Dipankar Nalui

Reputation: 1261

use pandas to convert json file to excel file.

import pandas
pandas.read_json("input.json").to_excel("output.xlsx")

Upvotes: 1

Abinash Panda
Abinash Panda

Reputation: 464

You can use pandas module along with an excel writer engine module such as xlwt or xlsxwriter for mapping json to XLS file. For example

If your json is 

[
   {"key_1":"foo1","key_2":"bar1","key_3":"foobar1"},
   {"key_1":"foo2","key_2":"bar2","key_3":"foobar2"}
]

To convert this json into XLS file

import pandas as pd
json_text = """
[
   {"key_1":"foo1","key_2":"bar1","key_3":"foobar1"},
   {"key_1":"foo2","key_2":"bar2","key_3":"foobar2"}
]
"""
df = pd.read_json(json_text)
df.to_excel('output.xls', index=False)

Upvotes: 8

Related Questions